About database model diagrams

Show me an example

To support database model diagramming, Microsoft Visio includes the Database Model Diagram template.

Using the Database Model Diagram template, you can

  • Create logical database model diagrams that can be mapped to a physical database.
  • Build projects that contain multiple submodels using different notation. After you build the project, you can use it to map your database model diagram to a physical database.

After you create a database model diagram, you can

  • Generate a new database from the diagram.
  • Update an existing database from the diagram.
  • Update the diagram according to changes in the physical database.
  • Update the source models in a project according to changes in the project.

Create a database model


About database modeling (Database model)

You can create logical and conceptual database model diagrams for relational and object-relational databases. After you create your diagram, you can use it to forward engineer a database.

Click to show or hide information. Database models

A database model is a simplified representation of the way data is stored. It hides specific storage details that are not useful for understanding the basic interrelationships of the stored data and highlights the essence of key logical relationships between data items.

Your Microsoft Visio product supports the goal of data modeling: to capture real-world information about an application domain accurately and in a way that is meaningful and understandable to both the user and the database designer.

Click to show or hide information. Database modeling activities

You can create conceptual database model diagrams using ORM notation, and logical database model diagrams using Relational and IDEF1X notation.

In addition, you can:

  • Reverse engineer a model from an existing database.
  • Import a database model from another program.
  • Update a database model diagram based on changes in the database.
  • Generate (or forward engineer) a new database from the database model diagram.
  • Create projects that contain multiple submodels using different notation.
  • Update an existing database based on changes in the database model diagram.
  • Update source model documents based on changes to the project file.

Click to show or hide information. Examples of database modeling activities

  • Design a new relational database or refine an existing database. Then update the database to incorporate changes in the new model or, if you make changes to the database, update the model to incorporate those changes.
  • Create conceptual models using ORM notation. Then build a logical model that can be mapped to a physical database.
  • Model databases in a team environment. Each team member can independently decide whether to use ORM or ER source models. Merge the source model documents together into a project document to create a single, comprehensive logical model. Use the project to diagram the logical model entirely or in parts, make changes, and add relationships between objects defined in different source models.
  • Ultimately, map the logical model to a physical database. In addition, migrate changes you make to the logical model back to the source model documents.
  • Create submodels that can be reused in many databases. Form a library of source model building blocks and mix and match them within the scope of a project to quickly put together large database models.

Click to show or hide information. Connections to physical databases

In addition to providing tools for creating database model diagrams, Microsoft Visio Modeling Tools deliver functionality for connecting to a wide range of database management systems (DBMSs).

You can connect to a DBMS to:

  • Reverse engineer an existing database to create a database model diagram.
  • Generate a new database from a database model diagram.
  • Update an existing database based on changes in the database model diagram.

Click to show or hide information. Stencils

The stencils in the database modeling solution include SmartShapes symbols that are programmed to behave in very specific ways in accordance with the chosen modeling method.

Click to show or hide information. Database model diagram customization

You can customize the behavior of the shapes that represent tables, columns, and relationships in your database model diagram. For example, you can:

  • Set the behavior when you remove a table or foreign key in a database model diagram.
  • Specify how foreign keys are propagated.
  • Determine how name conflicts are resolved and how default name prefixes are created for the elements in a database model diagram.

Click to show or hide information. Anchored windows

You can refine a database model diagram using the anchored windows associated with the template in which you are working. For example, you can use the Code window to view all code for a particular database platform that is associated with your model.

Click to show or hide information. Model validation

As you refine your database, you can validate it periodically to ensure that it is free of errors.

Click to show or hide information. Reports

You can create reports that describe your database model. During the database design process, reports make it easy for you to share information about the database with clients and other database designers. Because you can choose report types and control the level of detail a report contains, you can create reports that communicate the big picture or that include comprehensive information on more technical aspects of the design.


About the database solution templates (Database model)

The database modeling solution in Visio includes templates that you can use to

  • create logical database models for relational databases.
  • create conceptual database models based on facts and business rules.
  • reverse engineer an existing database to a model.
  • forward engineer a model to create a database schema.

Click to show or hide information. Database Model Diagram template

Use this template to create logical database models using relational notation or IDEF1X notation. In addition to diagramming your model, you can use this template to forward or reverse engineer physical databases.

This template is also used to group one or more source model drawings in a project. The source models in the project can then be merged into a single logical model which can be mapped to a physical database.

On the File menu, point to New, point to Database, and then click Database Model Diagram to open this template.

Click to show or hide information. ER Source Model template

Use this template to create logical database models in a collaborative environment. Using this template, you can create several source models that can be merged into a single, collective model (a database project) which you can map to a physical database.

On the File menu, point to New, point to Database, and then click ER Source Model to open this template.

Click to show or hide information. ORM Source Model template

Use this template to create conceptual database models that are based on facts and business rules. After you create your ORM source model, you can include it in a database project in order to build a logical model that can be mapped to a physical database.

On the File menu, point to New, point to Database, and then click ORM Source Model to open this template.

Click to show or hide information. Express-G

The Express-G database template is also included in the database solution. Database models that you create with this template cannot be mapped to a physical database.

On the File menu, point to New, point to Database, and then click Express-G to open this template.

Note The solutions available on your computer may vary depending on your installation of Microsoft Visio.


Create a database model diagram

Show me an example

  1. To start your database model diagram, do one of the following:

Click to show or hide information. Create a new model diagram from scratch

  1. On the File menu, point to New, point to Database, and then click Database Model Diagram or ER Source Model.
  2. On the Database menu, point to Options, and then click Document. In the Database Document Options dialog box, select the symbol set you want to use and other table and relationship options, and then click OK.

Click to show or hide information. Use a reverse engineered model diagram as a starting point

  1. On the File menu, point to New, point to Database, and then click Database Model Diagram or ER Source Model.
  2. On the Database menu, click Reverse Engineer.
  3. On the first screen of the Reverse Engineer Wizard, do the following:
  • Select the Microsoft Visio database driver for your database management system (DBMS). If you have not already associated the Microsoft Visio database driver with a particular 32-bit ODBC data source, do so now.
  • Select the data source of the database you're updating. If you have not already created a data source for the existing database, do so now.

Note When you create a new source, its name is added to the Data Sources list.

  • When you are satisfied with your settings, click Next.
  4. Follow the instructions in any driver-specific dialog boxes.

For example, in the Connect Data Source dialog box, type a user name and password, and then click OK.

  5. Select the check boxes for the type of information you want to extract, and then click Next.
  6. Select the check boxes for the tables (and views, if any) that you want to extract, or click Select All to extract them all, and then click Next.
  7. If you selected the Stored Procedures check box in step 5, select the procedures that you want to extract, or click Select All to extract them all, and then click Next.
  8. Select whether you want the reverse engineered items added automatically to the current page.
  9. Review your selections to verify that you are extracting the information you want, and then click Finish.

The wizard extracts the selected information and displays notes about the extraction process in the Output window.

  10. Create a diagram of your model by
  • Dragging the tables you want to view from the Tables and Views window onto the drawing page.
  • Selecting a table, and then on the Database menu, clicking Show Related Tables to view the tables related to the selected table.

Click to show or hide information. Import and refine an existing model

  1. On the File menu, point to New, point to Database, and then click Database Model Diagram or ER Source Model.
  2. On the Database menu, point to Import/Export, and then click Import <model type>.
  3. Type the path and file name for the model you want to import, or click the Browse button to locate the model file, and then click Open.
  4. Click OK in the Import dialog box.

Microsoft Visio imports the file and displays its progress in the Output window. The imported tables are displayed in the Tables and Views window.

  5. In the Tables and Views window, select the tables you want to model, and then drag them onto the drawing page.

  2. Once you start a database model diagram, the real work of refining it begins. You can add

Click to show or hide information. Tables

  1. Open the database model or ER source model diagram.
  2. From the Entity Relationship or Object Relational stencil, drag an Entity shape onto the drawing page.

Click to show or hide information. Columns

  1. Open the database model or ER source model diagram.
  2. Double-click the table to which you want to add a column, and then in the Database Properties window, in the Categories list, click Columns.
  3. Do one of the following:
  • Click the Physical Name field of a blank line, and then type the name of the column. Press the ENTER or DOWN ARROW key and repeat for each additional column that you want to add to the table.
  • Click Add. To add several columns to the table, click Add several times.

The default name for the new column follows the naming conventions you specified in the Database Modeling Preferences dialog box. To change the name of the column, click the default column name, and then type a new name for the column. Repeat to add additional columns to the table.

  4. Click either Physical Data Types or Portable Data Types to display the data types your database model uses in the Data Type field.
  5. To change the data type for a column, click the column's Data Type field, and then select a data type from the list, or simply begin typing the data type, including any parameters, and press ENTER. For example, you can type decimal(8,2) or char(30).
  6. To prevent null values, select the Req'd check box.
  7. To specify that the column is a primary key, select the PK check box.

The database model diagram is updated automatically.

Note You can press the TAB key to move from cell to cell, and you can use the SPACEBAR to select and clear the Req'd and PK check boxes.

There are more column properties in addition to those that appear when you click the Columns category. To edit additional column properties, click Edit.

Click to show or hide information. Foreign key relationships

  1. Open the database model or ER source model diagram.
  2. Click the Connector tool [image] on the Standard toolbar. Position the Connector tool over the center of the parent table so that the table is outlined and drag to the center of the child table. When the outline appears on the child table, release the mouse button. Both connection points appear red, and any primary keys in the parent table are added as foreign keys to the child table.
  3. Define the relationship by editing its properties.

Click to show or hide information. How?

  1. Open the database model or ER source model diagram.
  2. Double-click the relationship whose properties you want to edit to open the Database Properties window.
  3. In the Database Properties window, click the category that contains the information you want to edit, and then make your changes.

The database model diagram is updated automatically.

Click to show or hide information. Data types

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the column to which you want to add a composite data type, and then in the Database Properties window, in the Categories list, click Columns.
  3. Click the name of the column to which you want to add a composite data type, and then click Edit.
  4. In the Column Properties dialog box, click the Data Type tab, and then select the composite data type from the User Defined Type list.
  5. When you are satisfied with your settings, click OK.

Click to show or hide information. Tip

You can also add a composite data type to a table by clicking the Data Type box for the column to which you want to add a composite data type and either clicking the composite data type or typing the name of the composite data type.

  3. To make other refinements to your diagram, you can

Click to show or hide information. Create indexes

  1. Open the database model or ER source model diagram.
  2. Double-click the table to which you want to add an index, and then in the Database Properties window, in the Categories list, click Indexes.
  3. Click New.
  4. In the Create Index dialog box, type a name for the index, and then click OK.
  5. In the Index Type list, select an option to create a unique or non-unique index.
  6. In the Available Columns list, select the name of each column you want to include in this index, and then click Add >.
  7. In the Indexed Columns list, select the Asc check box to create an index that has an ascending sort order, or clear it to create one that has a descending sort order.

The database model diagram is updated automatically.

Click to show or hide information. Create views

From the Entity Relationship or Object Relational stencil, drag a View shape onto the drawing page.

Click to show or hide information. Set extended properties for tables and views

Double-click the table or view whose extended properties you want to set, and then in the Database Properties window, in the Categories list, click Extended.

  4. You can also add code for the following:

Click to show or hide information. Check clauses

  1. Double-click the table to which you want to add a check clause, and then in the Database Properties window, in the Categories list, click Check.
  2. Click Add to open the Code Editor.
  3. On the Properties tab, type a name for the check clause.
  4. On the Body tab, enter SQL statements for the check clause. When you're finished, click OK.

The check clause is added to the Local Code list in the Code window.

Click to show or hide information. Stored procedures

  1. If the Code window is not already open, on the Database menu, point to View, and then click Code to open the Code window.

Note This command is available only in the Database Model Diagram and ER Source Model templates.

  2. Click Global Code, and then click New to open the Code Editor.
  3. On the Properties tab, type a name for the code item. Under Type, click Stored Proc, Function, or Raw DDL.
  4. On the Body tab, type SQL statements for your code.

Note On the Notes tab, select the Generate as comment in DDL check box to add your notes as comments in a DDL script.

  5. When you're finished, click OK.

The code is added to the Global Code list in the Code window.

Click to show or hide information. Triggers

  1. Double-click the table to which you want to add a trigger, and then in the Database Properties window, in the Categories list, click Triggers.
  2. Click Add to open the Code Editor.
  3. On the Properties tab, type a name for the trigger.
  4. On the Body tab, enter SQL statements for the trigger. When you are finished, click OK.

The trigger is added to the Local Code list in the Code window.

  5. When you're satisfied with your diagram, you can do one of the following:
  • Use the Generate Wizard to create a database schema, whether you created a new model diagram from scratch or reverse engineered an existing model diagram.

You can generate a new database directly if your DBMS supports the direct creation of a new database. If your DBMS does not support direct creation, you must use a DDL script to create the physical database.

  • If you reverse engineered an existing database, you can use the Refresh Model Wizard to update your database model diagram based on changes to the physical database. You can also use the Update Database Wizard to update the physical database based on changes you've made in your model diagram.

  6. Use the Report Wizard anytime to generate reports that document your design or communicate information about the database to a client.


Create a database model diagram from scratch

  1. On the File menu, point to New, point to Database, and then click Database Model Diagram or ER Source Model.
  2. On the Database menu, point to Options, and then click Document. In the Database Document Options dialog box, select the symbol set you want to use and other table and relationship options, and then click OK.


About importing database models from another program

When you work in the Database Model Diagram, ER Source Model, or ORM Source Model template, you can import data models in the formats listed below.

After you import an existing model, its tables and views are displayed in the Tables and Views window.

Click to show or hide information. VisioModeler (formerly InfoModeler) 2.0, 3.0 or later

You can import VisioModeler (formerly InfoModeler) 2.0, 3.0 or later, using the following templates:

  • Dictionary model (.imd) files with the Database Model Diagram template.
  • Logical modeling diagram (.iml) files with the ER Source Model template.
  • Object Role Modeling (.imo) files with the ORM Source Model template.

All the characteristics of the object types, predicates, and constraints in the model are preserved.

Click to show or hide information. PLATINUM ERwin 2.6, 3.0, and 3.52 (.erx) files

You can import PLATINUM ERwin 2.6, 3.0, and 3.52 (.erx) files. An ERwin .erx file is a file exported by ERwin that contains information about a data model in text form.

Note If you have an ERwin .er1 file, use ERwin to save the file as an .erx file so you can import it into Visio.

When you import an .erx file, the following ERwin features are not imported:

  • Stored procedures
  • Custom trigger code
  • ERwin displays


Import a database model from another program

  1. On the File menu, point to New, point to Database, and then click Database Model Diagram or ER Source Model.
  2. On the Database menu, point to Import/Export, and then click Import <model type>.
  3. Type the path and file name for the model you want to import, or click the Browse button to locate the model file, and then click Open.
  4. Click OK in the Import dialog box.

Microsoft Visio imports the file and displays its progress in the Output window. The imported tables are displayed in the Tables and Views window.

  5. In the Tables and Views window, select the tables you want to model, and then drag them onto the drawing page.


About reverse engineering an existing database

With the Database Model Diagram template you can create a new database model by extracting (or reverse engineering) all or part of the schema of an existing database.

The Reverse Engineer Wizard lets you extract all or part of the schema from an existing database that you specify.

It leads you through the following tasks:

  • Connecting to the target database management system (DBMS) that you want to reverse engineer
  • Specifying the platform-specific database objects that you want to reverse engineer, such as tables, primary keys, indexes, and database code
  • Extracting, analyzing, and validating the schema into a database model diagram that you can review and modify

Click to show or hide information. Information the Reverse Engineer Wizard extracts

The Reverse Engineer Wizard can extract the following:

  • Tables
  • Primary keys
  • Indexes
  • Foreign keys
  • Triggers
  • Check clauses
  • Views
  • Stored procedures

The information that the wizard can extract depends upon the capabilities of your DBMS. If your DBMS makes some of the schema definition available, Microsoft Visio makes those options available in the Reverse Engineer Wizard and makes the other options unavailable.

Click to show or hide information. Where the Reverse Engineer Wizard puts the information it extracts

The Reverse Engineer Wizard notifies you of its progress in the Output window. When the extraction is complete, the wizard displays the extracted structures in the Tables and Views window.

To add the tables to the database model diagram, you drag the tables you want to view from the Tables and Views window onto the drawing page.

Note To view the tables related to a particular table without viewing the entire model diagram, right-click the table and click Show Related Tables.

Click to show or hide information. Additional tables extracted when reverse-engineering a Microsoft Access database

When reverse-engineering an Access database, you may notice additional tables extracted during the reverse-engineering process if you are working with a database originally created from a database model. For a database that has been generated from a database model, updated, and then reverse-engineered, unexpected tables may appear in the resulting database model diagram. Such tables are created to hold data that cannot be migrated to the new schema or in cases when the schema update is interrupted part way through the process. You can review such tables and use or delete as appropriate.

Click to show or hide information. Connecting to a database management system (DBMS)

Microsoft Visio uses one of its own database drivers, in conjunction with a vendor-supplied 32-bit ODBC driver, to connect to and extract information from a database.

To connect to a database, you must

  • Select an installed Visio database driver.
  • Associate a 32-bit ODBC driver with the installed Visio database driver.
  • Select or create a 32-bit ODBC data source.

Click to show or hide information. When to set up database drivers and associations

You can set up drivers at these times:

  • Before you run the Reverse Engineer Wizard.
  • While you are running the Reverse Engineer Wizard.

Note For best results, set your default driver to the target database that you want to reverse engineer before you run the Reverse Engineer Wizard. This step ensures that the wizard will map the native data types correctly and that all the code extracted by the wizard will be correctly displayed in the Code window.


Reverse engineer an existing database

  1. On the File menu, point to New, point to Database, and then click Database Model Diagram or ER Source Model.
  2. On the Database menu, click Reverse Engineer.
  3. On the first screen of the Reverse Engineer Wizard, do the following:
  • Select the Microsoft Visio database driver for your database management system (DBMS). If you have not already associated the Microsoft Visio database driver with a particular 32-bit ODBC data source, do so now.
  • Select the data source of the database you're updating. If you have not already created a data source for the existing database, do so now.

Note When you create a new source, its name is added to the Data Sources list.

  • When you are satisfied with your settings, click Next.
  4. Follow the instructions in any driver-specific dialog boxes.

For example, in the Connect Data Source dialog box, type a user name and password, and then click OK.

  5. Select the check boxes for the type of information you want to extract, and then click Next.
  6. Select the check boxes for the tables (and views, if any) that you want to extract, or click Select All to extract them all, and then click Next.
  7. If you selected the Stored Procedures check box in step 5, select the procedures that you want to extract, or click Select All to extract them all, and then click Next.
  8. Select whether you want the reverse engineered items added automatically to the current page.
  9. Review your selections to verify that you are extracting the information you want, and then click Finish.

The wizard extracts the selected information and displays notes about the extraction process in the Output window.

  10. Create a diagram of your model by
  • Dragging the tables you want to view from the Tables and Views window onto the drawing page.
  • Selecting a table, and then on the Database menu, clicking Show Related Tables to view the tables related to the selected table.


About tables (Database model)

You can add new tables to and remove existing tables from the database model diagram, and you can edit table properties.

All of your changes are reflected in the database schema when you generate a new database or update an existing one.

Click to show or hide information. Parent and child tables

The originating table in a foreign key relationship is known as the parent; the table that assumes or inherits attributes from the parent table's primary key is known as the child. Attributes inherited from the parent table appear as foreign keys in the child table.

More generally, if Table A includes a foreign key whose values must occur in the primary key values of Table B, then A is said to be the referencing table and B is the referenced table. While the terms "parent" and "child" correspond to "referenced" and "referencing," it is possible to declare a foreign key relationship from an existing column (or columns) in Table A to the primary key of Table B. In this case, no additional columns are added to Table A, or inherited from Table B.

Properly viewed, a foreign key relationship is just a subset constraint, indicating that the population of the foreign key in the referencing table must always be included in the population of the primary key of the referenced table.

Click to show or hide information. Model notation

The way tables appear depends on whether you decide to display the model in IDEF1X or Relational notation, the level of detail you want to display, and whether you display relationships by using Crow's Foot notation.

Tables displayed in

  • IDEF1X notation appear in the diagram as rectangles, and a table's conceptual name appears above the rectangle.
  • Relational notation also appear as rectangles, but a table's conceptual name appears in a shaded section at the top of the table.

Click to show or hide information. Independent and dependent IDEF1X tables

IDEF1X tables are classified as independent or dependent.

Independent tables include no foreign keys within their primary keys and appear in the diagram as rectangles with square corners.

Dependent tables include one or more foreign keys within their primary keys and appear as rectangles with rounded corners.

Note In models that use Relational notation, there is no graphical notation that indicates whether a table is independent or dependent.

Click to show or hide information. Code related to tables

You can apply check clauses and triggers to tables in your database model.

A check clause is code applied to a table or column that enforces constraints.

A trigger is code associated with specific database tables that is invoked when you insert, update, or delete rows of the associated table.


Add a new table (Database model)

  1. Open the database model or ER source model diagram.
  2. From the Entity Relationship or Object Relational stencil, drag an Entity shape onto the drawing page.


Edit the properties of a table (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table whose properties you want to edit to open the Database Properties window.
  3. In the Database Properties window, click the category in the Categories list that contains the information you want to edit, and then make your changes.

The database model diagram is updated automatically.


Remove a table (Database model)

  1. Open the database model or ER source model diagram.
  2. On the drawing page, click the table you want to remove, and then press DELETE.
  3. In the Delete Object dialog box, do one of the following:
  • Click Yes if you want to remove the table from the model and the Tables and Views window.
  • Click No to remove the table from the drawing page but not from the model or the Tables and Views window.

Note If you do not see the Delete Object dialog box when you delete an object from the diagram, on the Database menu, point to Options, click Modeling, and then on the Logical Diagram tab in the Database Modeling Preferences dialog box under When removing an object from the diagram, click Ask User What To Do.

Alternatively, if you want to remove the table from the model, click the table in the Tables and Views window and press the DELETE key.


About columns in a table (Database model)

To refine the tables in your database model diagram, you can add or remove columns, edit column properties, and change the order of the columns in a table.

Click to show or hide information. Primary and foreign keys

You can declare a column or set of columns in a table as a primary or foreign key.

Primary keys have unique values that identify instances of the table rows in a database. Each table must have a primary key to pass logical validation.

Foreign keys are columns, or sets of columns, whose population must be included in the population of another table's primary key.

Click to show or hide information. Indexes

Indexes optimize the process of retrieving data from a database. You can edit the properties of an index. Primary keys automatically contain unique indexes. Other columns can include a non-unique index.

Click to show or hide information. Code related to columns

You can apply check clauses to columns in your database model diagram.

Click to show or hide information. Default column name suffixes

You can edit default column name suffixes in the Database Modeling Preferences dialog box.


Add columns to a table (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table to which you want to add a column, and then in the Database Properties window, in the Categories list, click Columns.
  3. Do one of the following:
  • Click the Physical Name field of a blank line, and then type the name of the column. Press the ENTER or DOWN ARROW key and repeat for each additional column that you want to add to the table.
  • Click Add. To add several columns to the table, click Add several times.

The default name for the new column follows the naming conventions you specified in the Database Modeling Preferences dialog box. To change the name of the column, click the default column name, and then type a new name for the column. Repeat to add additional columns to the table.

  4. Click either Physical Data Types or Portable Data Types to display the data types your database model uses in the Data Type field.
  5. To change the data type for a column, click the column's Data Type field, and then select a data type from the list, or simply begin typing the data type, including any parameters, and press ENTER. For example, you can type decimal(8,2) or char(30).
  6. To prevent null values, select the Req'd check box.
  7. To specify that the column is a primary key, select the PK check box.

The database model diagram is updated automatically.

Note You can press the TAB key to move from cell to cell, and you can use the SPACEBAR to select and clear the Req'd and PK check boxes.

There are more column properties in addition to those that appear when you click the Columns category. To edit additional column properties, click Edit.


Edit the properties of a column (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the column you want to edit, and then click Columns in the Categories list in the Database Properties window.
  3. Click the name of the column you want to edit, and then click Edit.
  4. In the Column Properties dialog box, click the tab that contains the information you want to edit, and then make your changes.
  5. When you are satisfied with your settings, click OK.


Remove a column (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the column you want to remove, and then click Columns in the Categories list in the Database Properties window.
  3. Click the name of the column you want to remove, and then press the DELETE key or click Remove.

The database model diagram is updated automatically.

Click to show or hide information. Tip

To remove a series of columns, click the name of the first column you want to remove, hold down the SHIFT key, click the name of the last column you want to remove, and then press the DELETE key or click Remove.


Edit or create a primary key (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table to which you want to add a primary key, and then in the Database Properties window, in the Categories list, click Primary ID.
  3. In the Available Columns list, select the column you want to set as the primary key, and then click Add > to move the column to the Primary ID list.

The database model diagram is updated automatically.

Note You can set primary keys to appear at the top of table or in the actual, physical order. On the Database menu, point to Options, click Document, click the Table tab, and select an option under Order.


About foreign key relationships (Database model)

The Relationship shape is a one-dimensional connector that you can glue between two table shapes to define a foreign key (FK) relationship between them. Relationships indicate visually how the tables in your database model diagram interact with one another. Editing relationship properties enables you to control how data is stored in and retrieved from the database. Relationships in a model are characterized in terms of direction, referential integrity, and cardinality.

As you refine your database model diagram, you can

  • Add and remove foreign key relationships between tables.
  • Edit relationship properties.

Click to show or hide information. About parent and child relationship

The originating and receiving tables determine which table is the parent and which is the child.

The parent is the originating table in a relationship.

The child is the receiving table that assumes or inherits attributes from the parent table.

When you add a relationship to your model, to establish the relationship's direction, you connect first to the parent table and then to the child table.

Click to show or hide information. About attributes, referential integrity, and cardinality

An attribute is a column of a table (relational model) or a property of an entity (entity relationship model). Attributes inherited from the parent table appear as foreign keys in the child table.

Referential integrity is a relational database principle that requires a child table's foreign key values to be null or to match the parent table's primary key values. Setting a parent/child relationship between tables enables you to specify a referential integrity constraint, which ensures that the population of the referencing foreign key will always occur in the population of the referenced primary key. You can also specify the referential action that occurs if a user attempts to violate the constraint (by deleting or updating a value from the primary key, or by adding or updating a value for the foreign key).

The cardinality of a relationship describes how many records in a parent table can be directly related to records in a child table. Cardinality is expressed in terms such as one-to-one, one-to-many, many-to-one, or many-to-many.

Click to show or hide information. How the relationship's direction is displayed

The notation for your database model diagram determines how the relationship's direction is displayed.

In Relational notation, an arrow points to the parent table.

In IDEF1X notation, when Drawn With Crow's Feet is selected on the Relationship tab of the Database Document Options dialog box, a dot or crow's foot symbol indicates the child table.

Relational notation example

In Relational notation, the arrow at the end of the relationship line points to the parent table.

IDEF1X notation example

In IDEF1X notation, the large dot at the end of the relationship line indicates the child table.


Create a foreign key relationship between tables with the Relationship shape (Database model)

Click to show or hide information. IDEF1X notation

  1. Open the database model or ER source model diagram.
  2. Drag a Relationship shape onto the drawing page.
  3. Drag the endpoint without the circle to the parent table. When the table is outlined, release the mouse button.
  4. Drag the endpoint with the circle to the child table. When the table it is outlined, release the mouse button.

Both connection points appear red, and any primary keys in the parent table are added as foreign keys to the child table.

Click to show or hide information. Relational notation

  1. Open the database model or ER source model diagram.
  2. Drag a Relationship shape onto the drawing page.
  3. Drag the endpoint with the arrow to the parent table. When the table is outlined, release the mouse button.
  4. Drag the endpoint without the arrow to the child table. When the table is outlined, release the mouse button.

Both connection points appear red, and any primary keys in the parent table are added as foreign keys to the child table.


Edit the properties of a relationship (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the relationship whose properties you want to edit to open the Database Properties window.
  3. In the Database Properties window, click the category that contains the information you want to edit, and then make your changes.

The database model diagram is updated automatically.


Remove a relationship (Database model)

  1. Open the database model or ER source model diagram.
  2. In the diagram, click the relationship you want to delete, and then press DELETE.
  3. In the Delete Object dialog box, do one of the following:
  • Click Yes if you want to remove the foreign key from the model.
  • Click No to remove the relationship from the drawing page and leave the foreign key in the model.

Note If you do not see the Delete Object dialog box when you delete an object from the diagram, on the Database menu, point to Options, click Modeling, and then on the Logical Diagram tab in the Database Modeling Preferences dialog box under When removing an object from the diagram, click Ask User What To Do.


Reestablish foreign key relationships in a project (Database model)

  1. Reverse engineer a large database into different source models using the ER Source Model or ORM Source Model templates.
  2. Create a new database model project file.
  3. On the Database menu, point to Project, and then click Add Existing Document.
  4. In the Add Document To Project dialog box, select the source models created in step 1, and then click OK.
  5. On the Database menu, point to Project, and then click Build to create a collective model containing all of the elements in all of the source models you added to the project in step 4.
  6. On the Database menu, point to Model, and then click Refresh.
  7. In the Refresh Model wizard, choose the database from which the source models were reverse engineered, and then follow the instructions in the wizard.
  8. Right-click each table and click Show Related Tables.

Foreign key relationships will be reestablished and made visible on the model diagram.

  9. On the Database menu, point to Project, and then click Update Source Models to map the foreign key relationships back to the source models.


About data types (Database model)

You can set data type properties for any column. A column's data type specifies

  • The kind of data the column can contain.
  • The types of operations you can perform on the data in the column.
  • Column characteristics, such as physical storage requirements.

Before you set data type information in your database model, you should be familiar with the data types and storage requirements supported by your target DBMS. Refer to the documentation for your target DBMS to determine which data type to assign.

Note The Microsoft Visio data type support ensures that the database schemas you generate from your models are as efficient as possible.

Click to show or hide information. Portable versus physical data types

Portable data types are not specific to a particular database management system (DBMS).

When you generate a schema, you can map generic portable data types to multiple databases.

Physical data types are specific to a particular DBMS.

Click to show or hide information. Portable data types are automatically mapped

When you generate a database schema, Visio maps portable data types to the closest data type supported by the DBMS you specify. This feature offers flexibility if you are designing a database model that will be implemented on more than one type of DBMS, or if you are transferring a legacy database from one DBMS to another.

Click to show or hide information. Simple data types

Simple data types support one data value per column. You can work with simple data types in both relational and object-relational database model diagrams.

Click to show or hide information. Composite data types and collection types

You can use composite data types and collection types in object-relational database models.

Composite data types allow you to encapsulate fields that contain multiple values of different data types. Composite data types appear as distinct elements in a diagram and can be specified as the data type. You can use composite data types to create typed tables or views.

Collection types allow multiple instances of a given value to be stored in a field. You can assign them to any column in any table, but unless you display physical data types in the model, there is no way to see where they are used.

Both composite data types and collection types can optimize the performance of a database by storing data in a single entity, rather than by using foreign keys and secondary tables.

Click to show or hide information. User-defined data types

When you work with portable data types, you can use the default data types or define a new data type. User-defined data types can save you time and ensure consistency by providing a reusable data type that has a standard definition.


Create a composite data type (Database model)

  1. Open the database model or ER source model diagram.
  2. From the Object Relational stencil, drag a Type shape into the diagram.
  3. Double-click the Type shape to open the Database Properties window.
  4. In the Categories list, click Definition. Under Composite Type, click a type. To edit the composite data type name, type a new name in the Name box.
  5. To edit the other properties of the composite type, click the appropriate category in the Database Properties window and enter the information.

The database model diagram is updated automatically.


Create a user-defined data type (Database model)

  1. Open the database model or ER source model diagram.
  2. On the Database menu, click User Defined Types.
  3. In the User-Defined Types dialog box, click Add.
  4. In the Add New User-Defined Type dialog box, type a name, and then click OK.

Note To base the new data type on an existing one, select Copy From, and then select the name of an existing data type.

  5. Under Definition, specify characteristics for the new type.
  6. Click OK.


Set the data type for a column (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the column you want to edit, and then in the Database Properties window, in the Categories list, click Columns.
  3. Click the name of the column whose data type you want to set, and then click Edit.
  4. In the Column Properties dialog box, click the Data Type tab.
  5. Select the data type options you want to set for the column.
  6. When you are satisfied with your settings, click OK.

Note You can also change the data type on the Database Properties window by clicking the Data Type cell and selecting a new data type or by typing the data type, including any parameters, and pressing ENTER. For example, you can type decimal(8,2) or char(30). User-defined types and composite types are legal types that you can select or type directly.


Remove a composite type or user-defined type from the Types window (Database model)

  1. On the Database menu, point to View, and then click Types to open the Types window.
  2. Right-click the composite type or user-defined type you want to remove, and then click Delete.


About indexes (Database model)

Indexes help your database system find and sort records faster. Creating indexes for columns you plan to search frequently can significantly improve the performance of your database.

Click to show or hide information. Manually versus automatically created indexes

You can create indexes manually, and you can modify the indexes that Microsoft Visio automatically creates:

  • Primary keys (PK) automatically create a unique constraint.
  • Alternate keys (AK) in an IDEF1X model automatically create a unique index.
  • Inversion entries (IE) in an IDEF1X model automatically create a non-unique index or a constraint.

When Visio automatically creates an index for you, it also assigns it a name.

Note Some database applications have a limit on the type and number of indexes you can create per table. For specific information, see your database management system (DBMS) documentation. Visio checks for this limit during physical validation.

Click to show or hide information. How to define and edit indexes

You define indexes and edit their properties in the Database Properties window.

For example, you can edit the name of an index, change index options, rearrange the order of columns in an index, or set extended attributes for an index. Extended attributes for an index are driver-specific and are not supported by all DBMSs.


Create an index (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table to which you want to add an index, and then in the Database Properties window, in the Categories list, click Indexes.
  3. Click New.
  4. In the Create Index dialog box, type a name for the index, and then click OK.
  5. In the Index Type list, select an option to create a unique or non-unique index.
  6. In the Available Columns list, select the name of each column you want to include in this index, and then click Add >.
  7. In the Indexed Columns list, select the Asc check box to create an index that has an ascending sort order, or clear it to create one that has a descending sort order.

The database model diagram is updated automatically.


Edit index properties (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the index you want to edit, and then in the Database Properties window, in the Categories list, click Indexes.
  3. In the Index Name list, select the name of the index you want to edit.
  4. Edit the properties of the index by doing any of the following:
  • To specify a new name for the index, click Rename.
  • To specify a unique or non-unique index, choose an option in the Index type list.
  • To specify driver-specific index attributes, click Options.

The database model diagram is updated automatically.


Remove an index (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the index you want to delete, and then in the Database Properties window, in the Categories list, click Indexes.
  3. In the Index name list, select the name of the index you want to delete, and then click Delete.

The database model diagram is updated automatically.


About views (Database model)

Using a view, you can

  • Combine columns from several tables.
  • Control access to information in the database.
  • Perform operations on data from a column.
  • Access information needed by the users of a database without changing the structure of the tables in the database.
  • Improve database accuracy and efficiency, because the data in the view is inherited from the columns in the other tables.

For example, a view for invoices might combine columns from one table containing customer contact information, another containing employee information, and a third containing order details.

You can create views by dragging the View shape from the Entity Relationship or Object Relational stencil into your database model diagram. When you create a view by using the View shape, Microsoft Visio automatically creates SQL code to define the view. You can also use the Code Editor to define database-management system-specific views.


Add a view (Database model)

From the Entity Relationship or Object Relational stencil, drag a View shape onto the drawing page.


Assign columns from tables to a view (Database model)

  1. Open the database model or ER source model diagram that contains the view you want to add columns to.
  2. Double-click the view, and then in the Database Properties window, in the Categories list, click Columns.
  3. Do one of the following:
  • Click the Physical Name field of a blank line and type the name of the column. Repeat for each column you want to add to the view.
  • Click Add. The default name for the new column follows the naming conventions you specified in the Modeling Preferences dialog box. To change the name of the column, click the default column name and type a new name. Repeat for each column you want to add to the view.
  4. Click the name of the column whose properties you want to define, and then click Edit.
  5. In the View Column Properties dialog box, on the Source tab, click Known column in another table or view, and then click Change.
  6. In the Pick A Column dialog box, select a column from the list of tables and views in your database model diagram, and then click OK.
  7. When you are satisfied with your settings, click OK.
  8. Repeat steps 4 through 7 for each column you added in step 3.


Edit the properties of a view (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the view whose properties you want to edit to open the Database Properties window.
  3. In the Database Properties window, click the category that contains the information you want to edit, and then make your changes.

The database model diagram is updated automatically.


Edit the properties of a column in a view (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the view that contains the columns you want to modify, and then click Columns in the Categories list in the Database Properties window.
  3. Click the name of the column you want to modify, and then click Edit.
  4. In the View Column Properties dialog box, click the tab that contains the information you want to edit, and then make your changes.
  5. When you are satisfied with your settings, click OK.


Remove a view (Database model)

  1. Open the database model or ER source model diagram.
  2. On the drawing page, click the view you want to remove, and then press DELETE.
  3. In the Delete Object dialog box, do one of the following:
  • Click Yes to remove the view from the drawing page, as well as from the model and the Tables and Views window.
  • Click No to remove the view from the drawing page, but not from the model or the Tables and Views window.

Note If you do not see the Delete Object dialog box when you delete an object from the diagram, on the Database menu, point to Options, click Modeling, and then on the Logical Diagram tab in the Database Modeling Preferences dialog box under When removing an object from the diagram, click Ask User What To Do.

Alternatively, if you want to remove the view from the model, click the view in the Tables and Views window and press the DELETE key.


About extended attributes for tables, views, and columns (Database model)

You can set extended attributes for a table, view, or column.

Extended attributes are specific to a target database management system (DBMS), and enable you to control more precisely the way elements defined in your model are mapped to a target database.

For example, if you target your model to Microsoft SQL Server 7.0, you can specify the segment of the target database on which a table will be stored.


Set extended attributes for a table or view (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table or view whose extended attributes you want to define, and then in the Database Properties window, in the Categories list, click Extended.
  3. Click Edit.

Note If a particular DBMS does not support extended attributes, the Edit button is unavailable.

  4. In the driver-specific extended attributes dialog box, fill in the appropriate information, select any options that you want, and then click OK.


Set extended attributes for a column (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the column for which you want to define extended attributes, and then in the Database Properties window, in the Categories list, click Columns.
  3. Click the name of the column for which you want to define extended attributes, and then click Edit.
  4. In the Column Properties dialog box, click the Extended tab.
  5. Click Edit.

Note If a particular database management system (DBMS) does not support extended attributes, the Edit button is unavailable.

  6. In the driver-specific extended attributes dialog box, fill in the appropriate information, select any options that you want, and then click OK.


About code (Database model)

A database model diagram can include SQL code associated with objects in the database or with the database itself. Code can be in the form of stored procedures, triggers, check clauses, and so on.

As you refine your diagram, you can create new code as well as view and edit code that was extracted by the Reverse Engineer Wizard.

Click to show or hide information. What happens to the code when you reverse engineer a database

When you reverse engineer a database that contains code, the Reverse Engineer Wizard copies the SQL code as text and then adds it to the model as one of the following types of code:

  • Stored procedure
  • Function
  • Trigger
  • Check clause
  • View definition (but not the join criteria for views)
  • Raw DDL

After you reverse engineer a database that contains code, the Code window displays each code component in the order it was extracted.

The Code window displays the version of code specific to the platform from which it was reverse engineered. However, you can transfer code to a different platform to store multiple versions of code with your diagram.

Later, if you run the Generate Wizard or Update Database Wizard to create a DDL (Data Definition Language) script of the changes you made to the model, the code is generated in the script in the same order as it appears in the Code window.

Click to show or hide information. The difference between platform-specific code and portable code

As you work in a database model diagram, you can create, view, and edit either platform-specific or portable code associated with a database or its components.

Platform-specific code includes all the code extracted from a database and any new code you write that is intended for a particular database. You specify the database by selecting a database driver for your diagram. Only platform-specific code appears in the Code window.

Portable code is generic SQL code generated by Microsoft Visio and is not associated with any database driver.

When you generate or update a database from a model that includes portable code, a platform-specific copy of the code is generated for that database and is displayed in the Code window.

Click to show or hide information. How the Code window designates types of code

The Code window designates each of the following types of platform-specific code with a unique icon:

Stored procedures

User-defined functions and other procedures that can be invoked with a CALL statement and are stored in an executable format in the database.

Function

Procedural code that returns a value.

Triggers

Code associated with specific database tables that is invoked automatically whenever rows of the associated table are inserted, updated, or deleted.

Check clauses

Code applied to a table or column that enforces constraints.

View definitions

Code, usually containing SELECT statements, that assigns the results of a query to a named, table-like structure called a view. (Does not include the join criteria for views.)

Raw DDL

Data Definition Language code (that is, other SQL code that does not fall into one of the above categories). For example, synonyms or package constructs in an Oracle database.

Note Because not all platforms support all types of code, you need to know the capabilities of your database when you create code for a database model diagram. For example, although you can define a new view in your diagram, you cannot use it to generate a Corel Paradox database that has a view, because Paradox does not support views.

Click to show or hide information. The difference between global code and local code

Code appears in the Code window as global or local.

Global code refers to any stored procedure, function, view definition, or raw DDL code that is not associated with a specific table.

Local code refers to check clauses or triggers for a specific table or column in your diagram.

Click to show or hide information. How to implement code on multiple platforms

You can have multiple versions of the same code in your database model diagram to support the different databases you intend to generate, but the Code window shows only one version of code at a time.

To associate code with a different target database, you transfer it to the intended platform. You can switch between code implementations displayed in the Code window by changing the current driver.

Click to show or hide information. Saving code separately from your database model

You can save the code separately from your database model diagram by creating a mirror file.


About check clauses (Database model)

Check clauses enforce constraints on a table or column.

Click to show or hide information. Reverse engineered check clauses

After you reverse engineer a database, the Code window lists as local code all check clauses extracted from a database.

Click to show or hide information. New check clause for a table

When you create a new check clause for a table, the code is specific to a database platform. You specify the database by selecting a database driver for your model.

Click to show or hide information. New check clause for a column

When you create a new check clause for a column, you can

  • Define the check clause yourself for your intended target database.
  • Have Microsoft Visio generate one based on a range or set of values that you specify.

Auto-generated check-clause code for a column does not appear in the Code window.

If you generate or update a DDL script for your target database, the check-clause code for that database appears in the Code window.

Click to show or hide information. Customized check clauses

To customize the check clause for a particular database, you edit the code. When you edit the check-clause code, the Code window displays the code for the currently selected database driver.


Create a check clause for a table (Database model)

  1. Double-click the table to which you want to add a check clause, and then in the Database Properties window, in the Categories list, click Check.
  2. Click Add to open the Code Editor.
  3. On the Properties tab, type a name for the check clause.
  4. On the Body tab, enter SQL statements for the check clause. When you're finished, click OK.

The check clause is added to the Local Code list in the Code window.


Create a check clause for a column (Database model)

  1. Double-click the table that contains the column to which you want to add a check clause, and then in the Database Properties window, in the Categories list, click Columns.
  2. Click the name of the column to which you want to add the check clause, and then click Edit.
  3. In the Column Properties dialog box, click the Check tab.
  4. To generate a check clause based on a range of values, click Show value/range, and then enter values in the From and To boxes to define the range of the constraint. Click Add.
  5. To generate a check clause for a set of values, type a value in the Value box, and then click Add. Repeat for additional values, clicking Add after each.
  6. To customize or view the check-clause code, click Show check clause code. To modify the check clause, click Customize, and then type the code in the Check clause box.

The customized check clause is added to the Local Code list in the Code window.

  7. When you are satisfied with your settings, click OK.

Note An automatically generated check clause does not appear in the Code window, but a customized check clause does.


About triggers (Database model)

A trigger can be executed—or triggered—when specific database events occur, such as when a record is inserted, updated, or deleted.

For example, you can use a trigger to

  • Log audit information to a separate table.
  • Calculate derived information such as the extended cost of price times quantity.
  • Notify logged-on users of limits that have been exceeded.
  • Call a stored procedure.

Click to show or hide information. Creating a trigger

Because a trigger is associated with a specific database table, you create a trigger for a specific table (entity) shape in your database model diagram.

After you add a new trigger to a table in your diagram, the trigger code is listed under Local Code in the Code window.

Click to show or hide information. Reverse engineered triggers

When you reverse engineer a database that contains triggers, the trigger code is

  • Listed under Local Code in the Code window.
  • Accessible from the Triggers tab in the Database Properties window.


Create a trigger for a table (Database model)

  1. Double-click the table to which you want to add a trigger, and then in the Database Properties window, in the Categories list, click Triggers.
  2. Click Add to open the Code Editor.
  3. On the Properties tab, type a name for the trigger.
  4. On the Body tab, enter SQL statements for the trigger. When you are finished, click OK.

The trigger is added to the Local Code list in the Code window.


About global code (Database model)

Stored procedures, functions, and other platform-specific types of DDL (Data Definition Language) code are referred to as global code.

Click to show or hide information. How to add global code

You add global code in the Code Editor. You write new code by entering SQL statements and defining properties for each code item. Microsoft Visio adds the new code to the list of code items in the Code window.

Click to show or hide information. Database-specific code using raw DDL

Your target database might support types of code other than stored procedures and functions.

For example, if your target platform is an Oracle database, you can create a package to group together related variables, exceptions, and so on.

In your database model diagram, you can create such platform-specific code by specifying raw DDL as the code type.

When you use the Generate Wizard or the Update Database Wizard to generate a DDL script for a diagram that contains code marked as raw DDL, the wizard outputs whatever you defined on the Body tab of the Code Editor.


Create a stored procedure, function, or other global DDL code (Database model)

  1. If the Code window is not already open, on the Database menu, point to View, and then click Code to open the Code window.

Note This command is available only in the Database Model Diagram and ER Source Model templates.

  2. Click Global Code, and then click New to open the Code Editor.
  3. On the Properties tab, type a name for the code item. Under Type, click Stored Proc, Function, or Raw DDL.
  4. On the Body tab, type SQL statements for your code.

Note On the Notes tab, select the Generate as comment in DDL check box to add your notes as comments in a DDL script.

  5. When you're finished, click OK.

The code is added to the Global Code list in the Code window.


Synchronize code in a model that has a mirror file (Database Modeling)

  1. On the Database menu, point to Code, and then click Synchronize.

The Sync Code with Mirror Files dialog box opens. If a change has been made to any stored procedure, trigger, or check clause in the model, the code is listed under Differences.

  2. Select the stored procedure, trigger, or check clause you want to synchronize, and then click either Update Mirror File(s) Now or Update Model Now.
  3. Click Refresh to update the list of code that needs synchronizing, or click Close.


About models refreshed from databases (Database model)

To ensure that your model diagram reflects the current state of the database it represents, use the Refresh Model Wizard.

The Refresh Model Wizard

  • Connects to the target database.
  • Compares the model diagram to the database's schema.
  • Resolves any conflicts.

For each difference that the wizard detects, such as duplicate tables or new columns in tables, you can specify whether to update the model to reflect that change.

Click to show or hide information. Example of model updated from database

Model updated from database

The Refresh Model Wizard makes changes to a model (A) to synchronize it with the current state of a database schema (B).

Click to show or hide information. When you should update a model

You should update a model when`

  • You want to detect any changes that might have occurred in a database and incorporate them into your model diagram.
  • You want to update your model diagram if the original database has changed since the last time you reverse engineered it.
  • You used the Generate Wizard to create a DDL (Data Definition Language) script, which you executed to alter your database, and now you want to update the Visio diagram.

Click to show or hide information. Differences that the Refresh Model Wizard detects

The following table describes the differences the Refresh Model Wizard can detect between an object in a database schema and its corresponding object in the model diagram.

Database object | Type of change detected
Table | Deleting; changing its check clause, trigger, extended attribute, or table type (for typed tables) Note The Refresh Model Wizard does not detect new tables that are added to the database.
Column | Adding or deleting; changing its name, data type, nullability status, collection type, check clause, or extended attributes
View | Deleting or changing its definition Note The Refresh Model Wizard does not detect new views that are added to the database.
Primary key | Adding or deleting; changing its name or definition
Relationship (foreign key) | Adding or deleting; changing its name or definition (columns, cardinality, or referential integrity)
Index | Adding or deleting; changing its columns, collation, extended attributes, or uniqueness
Composite type | Deleting; changing its definition (named, distinct, domain) or alias (for collection type or aliased data type)
Field in a type | Adding or deleting; changing its name, definition (data types or, for object-relational, collection type), check clause, or extended attributes
Code | Deleting; changing code definitions


Refresh a model from an existing database (Database model)

  1. On the Database menu, point to Model, and then click Refresh.

Note This command is available only in the Database Model Diagram template.

  2. On the first screen, do the following:
  • Select the Microsoft Visio database driver for your DBMS.
  • Select the data source of the database you're updating.

If you have not already created a data source for the existing database, do so now.

When you create a new source, its name is added to the Data Sources list.

  • When you are satisfied with your settings, click Next.
  3. Follow the instructions in any driver-specific dialog boxes.

For example, in the Connect Data Source dialog box, type a user name and password, and then click OK.

  4. On the next screen, select an item in the list of conflicts, and then select the resolution option you want to use:
  • No Change Do not change the model diagram.
  • Refresh Model Change the diagram to match the database schema.

Repeat for each item in the list. When you are finished, click Next.

  5. On the next screen, review the changes that the wizard will make. If you are satisfied with them, click Finish to accept the changes and update the model. If you are not satisfied with the changes, click Back.


About source models and projects (Database model)

You need an understanding of source models and projects if you want to create conceptual models using ORM notation or if you want to merge several source models into one collective database model in Microsoft Visio.

Click to show or hide information. What is a source model?

A source model is a Visio file (.vsd) in which you create your database model. Each source model file can contain a portion (or submodel) of the entire database model.

You can use the ER Source Model solution or the ORM Source Model solution to create a source model file.

Click to show or hide information. What is a project?

A project is a Visio file (.vsd file) that you create with the Database Model Diagram template. A project identifies all of the source model files (or submodels) that make up a single model. Projects can include ORM source models, ER source models, and other non-Visio files you want to keep together.

A project contains the collective database model based on the source models you specify. The project's model resolves any and all externally defined entities (objects).

All of the source model files in your project are displayed in the Project window, where you can:

  • Open a model file by double-clicking its name.
  • Manage the models in the project by right-clicking the model names and choosing a command.
  • Add as many source model files as you like.

Once you build a project, you can use it to diagram the entire database model or portions of the model, make changes to the model, and add relationships between objects defined in different source models.

Click to show or hide information. How can I use source models and projects?

Projects make it possible to merge several source models into a single, collective database model. Use projects to model databases in a team environment and reuse source models.

To merge database models contained in multiple source files, you build a project.

When you build a project, the contents of the source model files listed in the Project window are merged to form an integrated logical model. To ensure the integrity of your database model, your model is validated to make sure every object has a unique definition.

Click to show or hide information. How many source models can I include in a project?

You can add as many source models as you like. A project can contain both ER source models and ORM source models. In addition, you can add other types of files to a database model project.

When you add an existing file to a project, you add only a reference to the file; you are not adding a copy of the file to the project file.

Because you can share model files among projects, if you make changes to a file and save it, your changes affect any project that includes that file.

Click to show or hide information. How are source models mapped to a physical database?

Both ER and ORM source models must be associated with a project before you can generate or update a physical database.

Click to show or hide information. Can I update source models with changes I've made to the logical model?

Projects allow you to migrate changes made to the collective database model back to the source models.

For example, if you change the name of a table in the collective model, you can migrate the change back to a source model. If the change is migrated back to an ORM source model, the name of the object type is updated. If the change is migrated back to an ER source model, the physical name of the table is updated.

Notes

  • If you choose not to update the source models, you will lose your changes to the collective model when you rebuild the project.
  • If you are working in a project that contains multiple source models, only changes to table names and changes to column names will be migrated back to the source models.

Click to show or hide information. Can I use the same element that appears in multiple source models in a project?

Projects can contain elements that appear in multiple source models. When you have multiple instances of the same element in one or more source model files within a project, you must define the copies as external.

You can define external objects in two ways:

  • Establish relationships in the collective model between elements that exist in different source models.
  • Define an element in a source model that has the same name and properties as another element and specify it as external.

Note Specifying a table as external does not automatically make the columns in that table external. If there are columns in another table with the same definition, the project will not build. To avoid this problem, specify any columns in the external table that share definitions with other columns as external.

When you build a project's collective database model, references to all externally defined elements are resolved so that the collective model contains a single instance of the object that was used in the overlapping source models.

Click to show or hide information. What if two or more distinct elements in a project have the same name?

Sometimes two distinct object types or two distinct entities in a complex application domain have the same name and same definition. If you want these elements to be treated as distinct elements, you can give one of the elements a unique name space value.

Click to show or hide information. Can I reestablish foreign key relationships that span multiple source models?

When you reverse engineer a large schema into multiple source models, foreign key relationships between elements that span source models are typically not visible.

You can reestablish these relationships in two ways:

  • Build the project, refresh the model, and then update the source models.

Relationships are reestablished when the project is built. When you update source models, foreign key relationships and external entities are added where necessary.

  • Identify any elements in a source model that you know were also reverse engineered into another source model and define them and any columns they contain as external.


Create a new database model project

  1. On the File menu, point to New, point to Database, and then click Database Model Diagram.
  2. On the Database menu, point to View, and then click Project to open the Project window.
  3. On the File menu, click Save, type a name for the project, and then click the Save button.

Note Choose a file name that differentiates the project file from your database source models, for example, Retail Project.vsd.

  4. Add existing files to the project.

You can add existing ER or ORM source model files (.vsd files) or other documents of any file type that you want to associate with the project.

  5. Add new source model files to the project.
  6. To save your project, on the File menu, click Save.


Add an existing file to a database model project

  1. Open an existing project file, or create a new one.
  2. Do one of the following:
  • On the Database menu, point to Project, and then click Add Existing Document.
  • In the Project window, right-click Project, and then click Add Existing Document.

If the Project window isn't open, on the Database menu, point to View, and then click Project to open the window.

  3. In the Add Document to Project dialog box, select the name of the file you want to add.

Note You can add ER or ORM source model files (.vsd files) or other documents of any file type that you want to associate with the project.

  4. Click Open.


Open a new source model file in a project (Database model)

  1. Open an existing project file, or create a new one.
  2. Do one of the following:
  • On the Database menu, point to Project, and then click New ER Source Model to start a new ER source model file.
  • On the Database menu, point to Project, and then click New ORM Source Model to start a new ORM source model file.
  • In the Project window, right-click Project, and then click ER Source Model or ORM Source Model.

If the Project window is not open, on the Database menu, point to View, and then click Project.

  3. In the Save New <> Source Model As dialog box, type a name for the new source model, and then click Save.

The name of the new source model file is added to the project and a new source model file opens.


Remove a file from a project (Database model)

  1. If the Project window is not open, on the Database menu, point to View, and then click Project.
  2. In the Project window, right-click the file that you want to remove, and then click Remove Document.

When you remove a file from a project, only the name of the model file is removed from the list in the Project window. The file itself is not removed from your hard drive.


Set properties for a database model project

  1. Open an existing project file, or create a new one.
  2. On the Database menu, point to Options, and then click Document.
  3. In the Database Document Options dialog box, specify the options for your database model project.
  4. When you are satisfied with the settings, click OK.

Note The properties you set for a project are applied only to the collective model in the project file. You can set different properties for your ER and ORM source model files.


Update a source model in a project (Database model)

  1. In the database project file, on the Database menu, point to Project, and then click Update Source Models.
  2. When you are prompted, migrate changes to source models.


Build a project (Database model)

  1. Open an existing project file, or create a new one.
  2. On the Database menu, point to Project, and then click Build. If you are prompted, migrate your changes back to the source models.

The project is built, and the Output window displays any errors or warnings that occur during the build process.

  3. If the Tables window is not already open, on the Database menu, point to View, and then click.
  4. Select the tables that you want to display, and then drag them to the drawing page.
  5. Refine the collective model, and then map it to a physical database.

To refine your model and then map it to a physical database, you follow the same steps that you would use to create a database model.

  6. When you are satisfied with the collective model, close it, saving your changes if prompted.
  7. If you've made changes to the collective model, you are prompted to migrate the changes back to the source models. To migrate the changes, click Yes.

Notes

  • If you choose not to migrate the changes back to the source models, you will lose your changes to the collective model when you rebuild the project.
  • If you are working in a project that contains multiple source models, only changes to table names and changes to column names will be migrated back to the source models.


About Database schema and DDL script generation

Click to show or hide information. Database schema generation

Visio generates a database schema for a specific database management system (DBMS) by:

  • Gathering the information from the database model.
  • Checking the model for errors.
  • Checking the model to ensure that the information in the model does not violate any restrictions imposed by the target DBMS.
  • Mapping the validated model to an optimally normalized database schema.
  • Generating a DDL (Data Definition Language) script from your model, creating a new database by connecting online to your DBMS, or doing both simultaneously.

Click to show or hide information. DDL script generation

DDL scripts, written in SQL code, are used to create, modify, and delete database objects such as tables, columns, rules, and indexes.

You can use Visio to generate a text file of the DDL script that contains the database schema for the database. Visio does not require a 32-bit ODBC data source to generate a DDL script.

After you have generated a DDL script, you can open, review, and edit the script by using any text editor. After you create and review the DDL script, you can run the script from your DBMS.

Click to show or hide information. Direct creation of new databases

You can create a new database by selecting a target DBMS, creating a new database and data source name, connecting to the data source and then generating a database schema.

Note The Create Database option is available only if your server supports creation of a database by means of 32-bit ODBC drivers.

Click to show or hide information. DBMSs that do not support direct creation of databases

If you are generating a database schema for a DBMS that does not support the direct creation of a new database, you can:

  • Generate a DDL script, which your DBMS can use to generate the database.
  • Use your DBMS to create a new (empty) database on your server and instruct your Visio product to generate the database schema for it.
  • Instruct your Visio product to connect to an existing (empty) database using a data source that you prepared with the 32-bit ODBC Data Source Administrator, and then create the tables in the database.

The following database management systems do not support the direct creation of a database from Visio:

  • Some Informix database management systems, such as Informix Online 7.x and SE 5.x
  • All Oracle database management systems
  • IBM DB2 v 2.x and 5.x
  • Sybase SQL Server

Note When you select the drivers for these database management systems, the Create Database option on the second page of the Generate Wizard is unavailable.

Before you generate

Click to show or hide information. Make sure your model is error-free

Before you try to use the Generate Wizard, you should refine your model and make sure it is error-free by validating it.

Note If your model contains errors the Generate Wizard will not open. The Output window displays any errors.

Click to show or hide information. Install and configure database drivers

You must have installed and correctly configured the appropriate database software and drivers. If you have difficulty, your system administrator can assist you with:

  • Obtaining access rights to your particular desktop or SQL-based client/server database.
  • Installing and configuring the appropriate client software on your local drive for a client/server database, such as Oracle SQLNet.
  • Installing the correct vendor-supplied 32-bit ODBC (Open Database Connectivity) drivers for your DBMS on your local drive.
  • Configuring the vendor-supplied 32-bit ODBC database drivers correctly by using the Microsoft 32-bit ODBC Data Source Administrator.

Click to show or hide information. Determine the database generation method of your DBMS

Before you generate, you should determine the database generation method that works for your particular DBMS.

When it generates a database schema, your Visio product uses enhanced database drivers in conjunction with vendor-supplied ODBC drivers to connect online to your desktop or client/server database. The options available for generating a new database depend on the capabilities of your DBMS, and not all servers support the direct creation of a database by means of 32-bit ODBC drivers.


Generate a database schema (Database model)

  1. On the Database menu, click Generate to open the Generate Wizard.

Note This command is available only in the Database Model Diagram template.

  2. On the first page, do the following:
  • Select the Generate new database check box.
  • To generate a DDL script simultaneously, select the Generate a text file of the DDL script check box.
  • To store the generated database structure in the source document, select the Store current database image in model check box.

Selecting this option creates a record of the extracted database objects, which enables the Update Database and Refresh Model wizards to work correctly.

  • When you are satisfied with the settings, click Next.
  3. On the second page, select the Visio database driver for your DBMS.

If you have not already associated the Visio database driver with a particular 32-bit ODBC data source, do so now.

  4. After you select the Visio driver, do one of the following:
  • Click Create <item> to create a new database.

Click to show or hide information. How?

After you click Create <item>, do one of the following:

  • Type a valid data source name in the Data Source Name box if you want to generate a database schema for an empty database you prepared ahead of time by using the 32-bit ODBC Data Source Administrator.
  • Click New to create a new ODBC data source in the Create New Data Source wizard.

Note If you need more information, see your documentation for the 32-bit ODBC Data Source Administrator.

When you are satisfied with the settings, click Next.

  • Click <item> Already Exists to create a database schema in an existing database for which you have previously defined a data source name.

Click to show or hide information. How?

  1. After you click <item> Already Exists, click Next.
  2. Select the data source of the database in which you want to create the database schema.

If you have not already created a data source for the existing database, click New to do so now. When you create a new source, Visio adds its name to the Data Sources list.

  3. When you are satisfied with the settings, click Next.

Note The names of the options change depending on the drivers you have selected. When you have selected a driver for a DBMS that does not support direct creation of a new database, the Create <item> option is unavailable.

  5. Follow the instructions in any driver-specific dialog boxes.

For example, in the Connect Data Source dialog box, type a user name and password, and then click OK.

  6. On the next page, review the list of tables and views that will be created in the new database, and click Next to continue.

Note To make changes to the model, click Cancel, and then make changes in the diagram.

The Generate Wizard then validates the database schema.

  7. On the final page, review any warnings that resulted during the validation of the database schema. Click Finish to create the new database, or click Cancel to return to the Output window and correct any errors or warnings.
  8. If you create a script and you want to review the script immediately after it has been created, click Yes when you are prompted to view the generated DDL script.

Review the script carefully before you run it in your DBMS. You can open, review, and edit the script in any text editor.

Note If you modified and then ran the DDL script that your Visio product generated, you must synchronize the tables in the model with the tables in the new database.


About databases updated from models (Database model)

Click to show or hide information. What the Update Database Wizard does

When you use the Update Database Wizard you can change the structure of a database to match the contents of your model diagram.

The Update Database Wizard does the following:

  • Connects to the target database.
  • Displays the conflicts between the current database schema and your model diagram, and prompts you to resolve any conflicts by keeping or discarding changes.
  • Calculates the net changes to a database schema based on the diagram.
  • Generates a DDL (Data Definition Language) script or directly alters the database.

Click to show or hide information. Three-way comparison for synchronizing

To synchronize a model diagram and database, Visio performs the following steps, in a process called three-way comparison:

  • Compares the current state of the database to a snapshot of the original state of the database when you extracted the schema
  • Compares the original state of the database with your model diagram
  • Compares the current state of the database with your model diagram

These comparisons detect any discrepancies between the model diagram and database schema so that you can merge changes and update one or the other as necessary.

For example, someone may have deleted a table from the database since the last time you extracted its schema. When you run the Update Database Wizard, it detects that the table was deleted and lists it as a conflict.

Three-way comparison with database model diagram (labeled A), database in current state (B), and database original state (C)

The automatic three-way comparison keeps a model (A) synchronized with a database (B) by comparing the current model and database schema with a snapshot of the original state of the database schema (C) at the time you extracted it.

Click to show or hide information. When you should update a database

You should update a database in any of these cases:

  • You reverse engineered the database, and now you want to forward engineer the changes in your model diagram to the database.
  • You created a new database model diagram from scratch that you want to insert into an existing database.

Click to show or hide information. How the database can be updated

The Update Database Wizard provides the following two ways to update a database:

Directly The wizard connects to the target database and directly alters an existing database schema or creates a new one.

By means of a DDL script The wizard creates a DDL script of the changes you want to make to the database. You can then execute the script from your database application.

If the wizard cannot directly generate update statements for the target database, it might also create a DML (Data Manipulation Language) script that transfers data from the existing tables to the newly created ones.

Database model diagram (labeled A) with arrrow to database (B), and arrow to DLL script (C)

Based on the refinements you have made to a model diagram (A), the Update Database Wizard makes changes directly to a database (B) or creates a DDL script (C) that you can execute to make changes to a database (B).

Click to show or hide information. Before you update the database schema

  • Verify that your model is valid. If the Update Database Wizard detects errors, it prompts you to check your model.
  • Verify that your system is set up correctly—that you have the appropriate access rights to the target desktop or client/server database, and that all the necessary drivers and client software your target database requires are available.
  • Verify the safety measures you use to protect your data.

Click to show or hide information. Differences that the Update Database Wizard detects

The following table describes the differences that the Update Database Wizard can detect between an object in a database schema and its corresponding object in the model diagram.

Database object | Type of change detected
Table | Deleting from model or database; adding to model; changing its name in model; changing its check clause, trigger, extended attribute, or table type (for typed tables) in model or database
Column | Adding to or deleting from model or database; changing its name, data type, nullability status, collection type, check clause, or extended attributes
View | Deleting from model or database; adding to or changing its name in model; changing its definition Note The Update Database Wizard does not detect new views that are added to the database.
Primary key | Adding to or deleting from model or database; changing its name or definition
Relationship (foreign key) | Adding to or deleting from model or database; changing its name or definition (columns, cardinality, or referential integrity)
Index | Adding to or deleting from model or database; changing its columns, extended attributes, or uniqueness in model or database; changing collation in database
Composite type | Deleting from model or database; adding to or changing its name in model; changing its definition (named, distinct, or domain) or alias (collection type or aliased data type) in model or database
Field in a type | Adding to or deleting from model or database; changing its name, definition (data types or, for object-relational, collection type), check clause, or extended attributes
Code | Deleting from model or database; adding to or changing the name in model; changing code definitions Note The Update Database wizard does not detect new code (procedures or functions) that is added to the database.


Update an existing database from a model (Database model)

  1. On the Database menu, click Update.

Note This command is available only in the Database Model Diagram template.

  2. On the first wizard page, do the following:
  • Select the Update the database check box.
  • Select the Detect changes in the database check box.
  • To write discrepancies to a file, select the Log conflicts to file check box, and then specify a file name and path.
  • To create a DDL script as well, select the Generate a text file with the DDL script check box, and then enter a file name.
  • When you are satisfied with your settings, click Next.
  3. On the second wizard page, do the following:
  • Select the Visio database driver for your DBMS.

If you have not already associated the Visio database driver with a particular 32-bit ODBC data source, do so now.

  • Select the data source of the database you are updating.

If you have not already created a data source for the existing database, do so now.

When you create a new source, its name is added to the Data Sources list.

  • When you are satisfied with your settings, click Next.
  4. Follow the instructions in any driver-specific dialog boxes that appear.

For example, in the Connect Data Source dialog box, type a user name and password, and then click OK.

  5. On the next page, select an item in the list of conflicts, and then select the resolution option you want to use:
  • No change does not change either the model or the database.
  • Refresh model changes the model to match the database.
  • Update database changes the database to match the model.

Repeat for each item, and then click Next.

  6. The next page lists updates to the model diagram that you specified.

For example, if your model contains a table that is not in the database, and you instructed the wizard to update the model, the table will be deleted from the model.

If you are satisfied with your changes, click Next; if you are not satisfied with your changes, click Back.

  7. The next page lists updates to the database schema that you specified. Review the changes, and then click Next.

For example, if your diagram contains a table that is not in the database, and you instruct the wizard to update the database, the table is added to the database.

  8. On the final page, click Finish to accept the changes and update the database schema.

The wizard checks the diagram for semantic and implementation errors and writes a list of progress notes, errors, and warnings in the Output window.

  • If validation succeeds, the wizard connects to the database and updates it with the changes you specified. A message in the Output window notifies you when the process is complete.
  • If validation fails, click Cancel to exit the wizard. (Validation fails, for example, if a database table's primary key has been removed.) Correct the errors in the diagram before you run the Update Database Wizard again.


About database model validation

Click to show or hide information. When can I validate a database model diagram?

You can check the validity of your model at any time during modeling. Microsoft Visio checks the database model diagram for both logical and physical validity and displays progress notes, warnings, and errors in the Output window. Using the information in the Output window, you can quickly navigate to any warning conditions or errors in the diagram.

Click to show or hide information. When does Visio automatically validate a database model diagram?

When you build a project, or use the Generate, Refresh Model, or Update Database wizards, Visio automatically validates your diagram before taking any action.

Click to show or hide information. What happens during logical validation?

During logical validation Visio checks your model to ensure it adheres to generally accepted database design standards.

For example, using the same physical name for two columns in a table produces an error.

Click to show or hide information. What happens during physical validation?

During physical validation, Visio checks your model to make sure it complies with DBMS-specific requirements.

For example, an Oracle database management system (DBMS) allows only one column in a table to use the Long data type; if a table contains more than one column that uses this data type, validation will generate an error. It is possible to have a logically correct database model that contains physical errors, especially if you reverse engineer a legacy database to a new DBMS.

Click to show or hide information. What happens during conceptual validation of an ORM source model?

During conceptual validation, Visio checks your ORM source model to ensure it adheres to generally accepted conceptual design standards.

For example, a ternary predicate that has a uniqueness constraint spanning just one role produces an error during conceptual validation.

Click to show or hide information. How does Visio display validation information?

When you validate a database model diagram, Visio displays any progress notes, errors, or warnings in the Output window.

Notes

  • If the diagram contains any critical errors, you must fix them before you can use the Generate, Refresh Model, Update Database, or Transfer commands.
  • Warnings do not prevent you from using the Generate, Refresh Model, Update Database, or Transfer commands.


About model and database synchronization (Database model)

If the database schema has changed since the last time you generated it, you can update either the database or your database model diagram to keep them synchronized.

For example, in a large project in which several people are concurrently working in a database, someone could have added columns to a table or removed tables from the database after you extracted the schema on which your diagram is based.

Using the Refresh Model and Update Database Wizards, you can interactively compare a diagram to a selected database schema and then address any items that are not synchronized.

Note The Refresh Model and Update Database Wizards do not detect when new tables are added to the database. If new tables are added to the database and you want to add them to your model, open your model and rerun the Reverse Engineer Wizard, selecting the tables you want to add. If you do not know which table is new, select all in the Reverse Engineer Wizard, only the new tables will be added to the model.

Click to show or hide information. The difference between the Refresh Model Wizard and the Update Database Wizard

You can use both wizards to update your diagram based on a database schema. The key difference between these wizards is that only the Update Database Wizard can make changes to a physical database.

Click to show or hide information. Resolving discrepancies

Depending on which wizard you are using, you can resolve the discrepancy in one of these ways:

  • Ignore the conflict for the time being.
  • Update the model to match the current state of the database schema, an option in the Refresh Model Wizard and the Update Database Wizard.
  • Update the database schema to match the model, an option in the Update Database Wizard.
  • Return the database to the state it was in when you extracted the schema, an option in the Update Database Wizard.

You can choose, item by item, how to resolve each discrepancy that the wizard uncovers.

For example, suppose someone deleted a table from the database after the last time you extracted its schema. When you run the Refresh Model Wizard or the Update Database Wizard, it detects the deleted table and lists it as a conflict.

  • If you choose to update your model, the table is deleted from the model diagram.
  • If you choose to update the database, the table is added back to the database.

Click to show or hide information. Log file for documenting discrepancies

When you use the Update Database Wizard, you can document any conflicts or inconsistencies between the database schema and the model in a log file.


About database model reports

You can create, print, preview, and export comprehensive reports based on the contents of the current model on the drawing page. For example, you can create a report to:

  • Document your database design.
  • Summarize file and database statistics or to document the tables in your model.
  • List data types in an object-relational model.

In the New Report Wizard, you can select one of the following preformatted report types. Visio then automatically launches the Report dialog box, where you can select options for custimizing the report's contents, as well as for printing, previewing, and exporting the report.

Click to show or hide information. Constraint Type Report

Constraint type reports provide detailed information about the constraints in an ORM source model or in a project containing an ORM source model. For example, a constraint type report can include information about the location of the constraint and the text that appears in the Verbalizer window when you select the constraint.

When you create a constraint report you can specify:

  • The types of constraints you want to review.
  • The attributes, such as definition information, you want to include in the report.

Click to show or hide information. Fact Type Report

Fact type reports show information about the facts in an ORM source model or in a project containing an ORM source model.

When you create a fact type report, you can do the following:

  • Choose which facts to include in the report.

For example, you might decide to report on all facts associated with a particular object, or you might include only facts that have a certain input style.

  • Specify which information about the facts to include.

For example, you might choose to include a visual representation of the fact in the report.

Click to show or hide information. Object Type Report

Object type reports show information about the objects in an ORM source model or in a project containing an ORM source model.

When you create an object type report, you can:

  • Choose which object types to include in the report.

For example, you can generate a report that shows only those entity objects that don't have a reference mode assigned.

  • Specify which information about the object types to include.

Click to show or hide information. Statistical Report

Statistical reports provide a high-level summary of the contents of a database model diagram.

In a statistical report, Visio does the following:

  • Tallies the number and type of constraints, foreign keys, and attributes
  • Summarizes source file information for your model

When you create a statistical report, you can use the following terms to describe your model:

  • Entity relationship terms, such as entities, attributes, and relationships
  • Object-relational terms, such as tables, columns, and keys

Click to show or hide information. Supertype Report

Supertype reports provide information about the subtype and supertype definitions in an ORM source model or in a project containing an ORM source model.

When you create a supertype report, you can:

  • Choose the subtypes and supertypes to include in the report.
  • Specify which information about the subtypes and supertypes to include.

Click to show or hide information. Table Report

Table reports show how objects in your model, such as tables and columns, will appear in the database you generate or update from the model. Visio includes two types of table reports:

  • A conceptual table report describes your model using the conceptual names of objects specified in the various Properties dialog boxes.
  • A physical table report describes your model using the physical names of objects specified in the various Properties dialog boxes.

Click to show or hide information. Types Report

Types reports show the composite, user-defined, and built-in data types represented in your database model diagram.

You can also include attribute information, such as type names, base types, and derived types, by using the Attributes tab in the Report dialog box.

By default, your report is sorted by the class of data type and then by type name.


Generate a report (Database model)

  1. On the Database menu, click Report.

Note This command is available in the Database Model Diagram, ER Source Model, and ORM Source Model templates.

  2. In the New Report Wizard, select the type of report you want, click Next, and then click Finish.
  3. In the Report dialog box, define and format the information you want to include in your report. Specific options vary for each report type:

Click to show or hide information. Constraint Type Report

  • On the Filter tab, specify the constraints you want to show in your report.
  • On the Attributes tab, specify the attributes you want in your report.
  • On the Sort/Group tab, specify the report objects that will be used to sort the report contents. Only the options you checked on the Attributes tab appear on this tab as records you can sort or group.
  • On the Headers tab, create a report title.
  • On the Pagination tab, set page breaks and other formatting options.

Click to show or hide information. Fact Type Report

  • On the Filter tab, specify the facts that you want to include in your report.
  • On the Attributes tab, specify the attributes you want in your report.
  • On the Sort/Group tab, specify the report objects that will be used to sort the report contents. Only the options you checked on the Attributes tab appear on this tab as records you can sort or group.
  • On the Headers tab, create a report title.
  • On the Pagination tab, set page breaks and other formatting options.

Click to show or hide information. Object Type Report

  • On the Filter tab, specify the object types that you want to include in your report.
  • On the Attributes tab, specify the attributes you want in your report.
  • On the Sort/Group tab, specify the report objects that will be used to sort the report contents. Only the options you checked on the Attributes tab appear on this tab as records you can sort or group.
  • On the Headers tab, create a report title.
  • On the Pagination tab, set page breaks and other formatting options.

Click to show or hide information. Statistical Report

  • On the Preferences tab, select the notation you want to use in the report.
  • On the Headers tab, create a title for the report.
  • On the Pagination tab, set page breaks and other formatting options.

Click to show or hide information. Supertype Report

  • On the Attributes tab, specify the attributes you want in your report.
  • On the Headers tab, create a report title.
  • On the Pagination tab, set page breaks and other formatting options.

Click to show or hide information. Table Report

  • On the Preferences tab, specify the type of report you want (conceptual or physical) and the type of notation to use.
  • On the Attributes tab, specify the database, table, column, index, and foreign key attributes you want in your report.
  • On the Sort/Group tab, specify the report objects that will be used to sort the report contents.
  • On the Headers tab, create a title for the report.
  • On the Pagination tab, set page breaks and other formatting options.

Click to show or hide information. Types Report

  • On the Selection tab, specify the types you want to show in your report.
  • On the Attributes tab, specify the attributes you want in your report.
  • On the Sort/Group tab, specify the report objects that will be used to sort the report contents. Only the options you checked on the Attributes tab appear on this tab as records you can sort or group.
  • On the Headers tab, create a report title.
  • On the Pagination tab, set page breaks and other formatting options.
  4. Click Print, Preview, or Export to RTF to generate the report.
  5. Click Close.


Add a report title, a report subtitle, or group header text (Database model)

  1. Generate a report.
  2. In the Report dialog box, click the Headers tab, and then do one or more of the following:
  • To type header text, click in an edit box, and then type the text you want.
  • To insert a formatting code in the header text, click in an edit box, click Insert, and then select the information you want to include in the header.
  • To add line breaks to text, press Ctrl+Enter.
  • To delete text, select the text, and then press the Delete key.

Click to show or hide information. Where does the header text appear?

  • The Report title appears at the top of the first report page.
  • The Report subtitle appears beneath the report title on the first page.
  • The Group header appears between groups of records in your report. (Each group is listed on the Sort/Group tab.)
  3. To change the pagination, click the Pagination tab, and then choose the options you want.
  4. To preview the report, click Preview, then do one or more of the following:
  • To print the report, click Print.
  • To navigate between pages, click Next Page or Prev Page.
  • To change the number of pages being previewed, click One Page or Two Page.
  • To change the magnification of the view, click Zoom In or Zoom Out.
  • To change the report's contents, click Close to return to the Report dialog box.


Database Document Options dialog box (General tab)

Click to show or hide information. IDEF1X

Click to use IDEF1X symbols.

Click to show or hide information. Tips

To use Crow's feet notation, click the Relationship tab, and select the Crow's feet check box.

To use UML Cardinality notation, do the following:

  1. On the General tab, click Relational.
  2. Click the Relationship tab, clear the Crow's feet check box, select the Cardinality check box, and then click OK.

Click to show or hide information. Relational

Click to use physical schema symbols.

Click to show or hide information. Tips

To use Crow's feet notation, click the Relationship tab, and select the Crow's feet check box.

To use UML Cardinality notation, do the following:

  1. On the General tab, click Relational.
  2. Click the Relationship tab, clear the Crow's feet check box, select the Cardinality check box, and then click OK.

Click to show or hide information. Conceptual names

Click to display the conceptual name for a database object—as specified in the Database Properties window—for each entity or relationship.

Click to show or hide information. Physical names

Click to display the physical name for a database object—as specified in the Database Properties window—for each entity or relationship.

Click to show or hide information. Both

Click to display the physical and conceptual names for a database object—as specified in the Database Properties window—for each entity or relationship.

Click to show or hide information. Based on symbol set

For models that use IDEF1X notation, click to display the conceptual name for a database object—as specified in the Database Properties window—for each entity or relationship.

For models that use Relational notation, click to display the physical name for a database object—as specified in the Database Properties window—for each entity or relationship.

Click to show or hide information. Defaults

Click to make the current settings the default settings for the document (Set As), or to reset the default settings to those last set for the document (Restore) or to the Microsoft Visio defaults (Restore Original).


Database Document Options dialog box (Table tab)

Click to show or hide information. Primary keys

Select this check box to display primary keys in the model. Options under Order specify how primary keys appear. If this check box is cleared, a column designated as a primary key does not appear in the diagram unless the column has another property that must be displayed in the Annotations column.

Click to show or hide information. Foreign keys

Select this check box to display foreign keys in the model. If this check box is cleared, a column that is designated as a foreign key does not appear in the diagram unless the column has another property that must be displayed in the Annotations column.

Click to show or hide information. Alternate keys

Select this check box to display alternate keys in the model for any columns that have a unique index. If this check box is cleared, a column with an alternate key does not appear in the diagram unless the column has another property that must be displayed in the Annotations column.

Click to show or hide information. Indexes

Select this check box to display columns that have an index in the model (but not those with a unique index). If this check box is cleared, a column with an index does not appear in the diagram unless the column has another property that must be displayed in the Annotations column.

Click to show or hide information. Non keys

Select this check box to display all columns that are not keys in the model. If this check box is cleared, a column that is not a key does not appear in the diagram unless the column has another property that must be displayed in the Annotations column.

Click to show or hide information. Annotations

Select this check box to annotate columns in a model by displaying the following column property symbols:

Column | Relational notation | IDEF1X notation
Primary Key | PK | —
Foreign key | FKn | FK
Non-unique index Inversion entry | In | IEn
Unique index Alternate Key | Un | AKn

Click to show or hide information. Vertical line(s)

Select this check box to display a vertical line in the database table shape to separate the column name from annotations and data types.

Click to show or hide information. Don't show

Click to hide information about column data types (the default).

Click to show or hide information. Show physical

Click to display physical data types in a model based on the current target database; these data types will be used in the physical model.

Click to show or hide information. Show portable

Click to display the portable data types. Portable datatypes are generically defined types that map to similar, compatible physical data types in different database systems.

Click to show or hide information. Primary keys at top

For data models that support the separate display of primary keys, click to display the table columns with the primary keys at the top.

Click to show or hide information. Draw separator line

For data models that support the separate display of primary keys, select this check box to display a horizontal separator between the last primary key column and the first non-primary key column.

Click to show or hide information. Physical order

Click to display the table component keys in the order they appear in the database. The primary key is not separated from other columns.

Click to show or hide information. Show IDEF1X optionality ‘(0)'

Select this check box to display the optionality symbol for models using IDEF1X notation.

Click to show or hide information. Defaults

Click to make the current settings the default settings for the document (Set As), or to reset the default settings to those last set for the document (Restore) or to the Microsoft Visio defaults (Restore Original).


Database Document Options dialog box (Relationship tab)

Click to show or hide information. Relationships

Select this check box to display relationship shapes in the model.

Click to show or hide information. Crow's feet

Select this check box to replace the one-to-many graphical symbol with a crow's foot notation in the model, making the relationship connector use Information Engineering notation.

Click to show or hide information. Cardinality

This option is available only if the Crow's Feet check box is cleared. Select this check box to display cardinality notation on relationship shapes.

Click to show or hide information. Tip

To use UML Cardinality notation, do the following:

  1. On the General tab, click Relational.
  2. Click the Relationship tab, clear the Crow's feet check box, select the Cardinality check box, and then click OK.

Click to show or hide information. Referential integrity

Select this check box to display symbols on relationship shapes to indicate referential integrity constraints.

Microsoft Visio displays the notation in this form: u:C, where "u" is the type of change (update or delete) and "C" is the type of action (No Action, Cascade, Set default, or Set NULL).

Notation | Triggered action
u | On update
d | On delete
R | No action (that is, Restrict)
C | Cascade
D | Set default
N | Set NULL
(Blank) | Do not enforce referential integrity

Click to show or hide information. Show verb phrase

Click to display role text on relationship shapes. You can specify the forward predicate role text (verb phrase) and inverse predicate role text (inverse phrase) in the Database Properties window for the Relationship shape.

Click to show or hide information. Forward text

Select this check box to display the forward predicate role text on relationship shapes.

Click to show or hide information. Inverse text

Select this check box to display the inverse predicate role text on relationship shapes.

Click to show or hide information. Show physical name

Click to display the name of relationships on the shapes' connecting line based on the name used by the target database.

By default, the name generated using the Database Modeling Preferences dialog box is displayed. For example, Parent + Child + Suffix.

To change the default name, on the Database menu, click Options, click Modeling, click the Logical Misc tab, and then select an option in the FK Name Generation Option list.

Click to show or hide information. Don't display name

Click to display no text on the relationship shapes' connecting lines.

Click to show or hide information. Defaults

Click to make the current settings the default settings for the document (Set As), or to reset the default settings to those last set for the document (Restore) or to the Visio defaults (Restore Original).


Database Drivers dialog box (Drivers tab)

Use these options to configure your Microsoft Visio database drivers. When you configure your Visio database drivers, you specify a default Visio database driver, default data type properties, and a default data type for column creation.

You can also specify some other database generation options that will apply to all database drivers.

You can configure individual database drivers by selecting a driver and clicking Setup. You can also select a default data source for the driver and set additional driver setting preferences.

Note A Microsoft Visio database driver is a supplied file that allows Visio to work better with a vendor-supplied database driver or interface, such as an ODBC driver or OLE DB provider. For an association between a Visio database driver and a data source to work properly, the database drivers you select must be designed to work with the ODBC driver or OLE DB provider selected when the data source was defined. For example, you would associate the Visio database driver for Oracle Server with a data source defined for use with an ODBC driver for Oracle databases.

When you reverse engineer a database, refresh a database model from a database, or generate or update a database, the list of available data sources is based on the Visio database driver associations you established in the Setup dialog box for the currently selected Visio database driver. If no associations were established for the driver, then all defined data sources will be listed.

Click to show or hide information. Default driver for Visio

Click the default driver that you want to be selected when you choose a Visio database diagram command.

Click to show or hide information. Setup

Click to display the Setup dialog box, where you can associate the selected Visio database driver with compatible data sources and specify driver-specific properties.


Database Drivers dialog box (Default Mapping tab)

Use these options to configure your Microsoft Visio database drivers. When you configure your Visio database drivers, you specify a default Visio database driver, default data type properties, and a default data type for column creation.

You can also specify some other database generation options that will apply to all database drivers.

You can configure individual database drivers by selecting a driver and clicking Setup. You can also select a default data source for the driver and set additional driver setting preferences.

Note A Microsoft Visio database driver is a supplied file that allows Visio to work better with a vendor-supplied database driver or interface, such as an ODBC driver or OLE DB provider. For an association between a Visio database driver and a data source to work properly, the database drivers you select must be designed to work with the ODBC driver or OLE DB provider selected when the data source was defined. For example, you would associate the Visio database driver for Oracle Server with a data source defined for use with an ODBC driver for Oracle databases.

When you reverse engineer a database, refresh a database model from a database, or generate or update a database, the list of available data sources is based on the Visio database driver associations you established in the Setup dialog box for the currently selected Visio database driver. If no associations were established for the driver, then all defined data sources will be listed.

Click to show or hide information. Category

Click a data type category. When you click a data type category, the options under Type change so that you can define the default property values for the portable data types used in the current model diagram.

Click to show or hide information. Type

Click a data type for the selected category. When you click a data type, the default property values for that type appear. These properties may include size, length, precision, and scale. You may edit the default values for these properties.

Click to show or hide information. Size

Click the type's storage size category.

Click to show or hide information. Length

Type the maximum number of bytes allowed for a column.

Click to show or hide information. Precision

Type the maximum number of digits used by the data type.

Click to show or hide information. Scale

Type the maximum number of digits to the right of the decimal point.

Click to show or hide information. Default category type for column creation

Click the category type that you want to use as the default type when adding new columns to a table in a database model diagram.


Connect Data Source dialog box

Click to show or hide information. Data source

Displays the currently selected data source.

Click to show or hide information. User

Type the user name with which to log on to a host or server database system.

Click to show or hide information. Password

Type the password with which to log on to a host or server database system.


Modeling Preferences dialog box (Logical Diagram tab)

Use these options to establish the default settings that Microsoft Visio uses for all database modeling sessions until new defaults are set.

Click to show or hide information. Always drop from model

Click to specify that deleting an Entity, View, or Type shape removes the entity, view, or type from the model, as well its visual representation (that is, the particular Entity, View, or Type shape) from the drawing page.

Click to show or hide information. Drop From Model Only If Last Occurrence On Diagram

Click to specify that deleting an Entity, View, or Type shape removes the entity, view, or type from the model only if no other visual representation of the table exists.

Click to show or hide information. Never Drop From Model

Click to specify that deleting an Entity, View, or Type shape removes only its visual representation (that is, the particular Entity, View, or Type shape) from the drawing page. An instance of the entity, view, or type remains in the Tables and Views window or Types window.

Click to show or hide information. Ask User What To Do

Click to specify that whenever you delete an Entity, View, or Type shape from the diagram, a message box appears asking whether to remove the shape but to retain an instance of the entity, view, or type in the Tables and Views window or Types window, or to remove both the shape and the entity, view, or type it represents from the diagram.

Click to show or hide information. Show relationships after adding table to diagram

Select this check box to display relationships for a new instance of a table that you add to the drawing page.

Click to show or hide information. Show relationships after adding type to diagram

Select this check box to display relationships for a new type that you add to the drawing page.

Click to show or hide information. Sync conceptual and physical names in new tables and columns when typing

Select this check box to synchronize the conceptual and physical names for new tables you create when you change either one in a diagram.


Modeling Preferences dialog box (Logical Misc tab)

Use these options to establish the default settings that Microsoft Visio uses for all database modeling sessions until new defaults are set.

Click to show or hide information. Propagate on add

Select this check box to create a foreign key relationship when you connect a relationship line between two tables. Selecting this check box creates a new column in the child table and identifies a foreign key relationship between the two tables

If this check box is cleared, the parent-to-child relationship is created, but the model will not be semantically valid until you modify the relationship to associate the parent with a child column. Then you can create a foreign key relationship with a column of a different name from that of the parent.

Click to show or hide information. Propagate on delete

Select this check box to delete any foreign key column(s) from a child table if the defined foreign key relationship is deleted from the model.

If this check box is cleared, only the relationship is dropped, and any columns specified as foreign keys are left in the child table.

Click to show or hide information. Name conflict resolution

Click the behavior to use when you add a foreign key with the same name as a column that already exists in the child table.

Click to show or hide information. Entity

Type the prefix that is used to form the default conceptual name of a table.

Click to show or hide information. Attribute

Type the prefix that is used to form the default conceptual name of a column.

Click to show or hide information. Table

Type the prefix that is used to form the default physical name of a table.

Click to show or hide information. Column

Type the prefix that is used to form the default physical name of a column.

Click to show or hide information. Type

Type the prefix that is used to form the default name of a type.

Click to show or hide information. Field

Type the prefix that is used to form the default physical name of a field.

Click to show or hide information. Primary key

For the logical model, type the suffix to append to all primary key names.

Click to show or hide information. Foreign key

For the logical model, type the suffix to append to all foreign key names.

Click to show or hide information. Index

For the logical model, type the suffix to append to all index names.

Click to show or hide information. Alternate key

For the logical model, type the suffix to append to all alternate key names.

Click to show or hide information. FK name generation option

Click how foreign key names are automatically generated. The suffix used is the text you enter in the Foreign key box under Default name suffixes.


User-Defined Types dialog box

Use these options to add, remove, and edit custom data types. When you work with portable data types, you can use the default specifications or define a user-defined data type for easy reuse.

User-defined data types can save time and ensure consistency by providing a reusable data type that has a standard definition.

For example, you can create user-defined types whose names match those of your DBMS. You can then use data type names that correspond to those of your target DBMS, although DBMSs that have reserved words may not allow this.

You can also use user-defined types to create data types that are subsets of broader categories.

You can copy definitions from existing user-defined data types to new ones.

Note Before you set data type information in your database model, you should be familiar with the data types and storage requirements supported by your target DBMS. Refer to the documentation for your target DBMS to determine which data type to assign.

Click to show or hide information. User-defined types

Lists existing user-defined data types. Click the user-defined type that you want to remove, rename, or redefine.

Click to show or hide information. Add

Click to open the Add New User-Defined Type dialog box, in which you can create a new user-defined type that has a default name and type definition.

You must name the new data type, and you must match it to a native portable data type used by Visio.

Click to show or hide information. Remove

Click to delete the selected user-defined type. If the type has been assigned to an object, a message appears asking whether to delete the user-defined type.

Click to show or hide information. Rename

Click to open the Rename User-Defined Type dialog box and rename the selected user-defined type.

Click to show or hide information. Category

Click a portable data type category available in Microsoft Visio.

Click to show or hide information. Type

For the selected category, click an applicable type.

Some values for Type also require Size, Length, Precision, or Scale values.

Click to show or hide information. Size

For the selected category and type, click a size.

Click to show or hide information. Length

Type the number of characters.

Click to show or hide information. Precision

Type the total number of digits (the number of digits to the left of the decimal point plus the number of digits to the right of the decimal point).

Click to show or hide information. Scale

Type the number of digits to the right of the decimal point.

Click to show or hide information. Description

Type text to describe the user-defined data type.


Add New User-Defined Type dialog box

Click to show or hide information. Data type name

Type a unique name for the new data type.

Click to show or hide information. Copy from

Select this check box to copy the definition of the user-defined data type selected in the list below.

Click to show or hide information. List

Lists the user-defined data types already defined in the model and available to base the new data type on. Click the user-defined type that you want to copy.


Rename User-Defined Type dialog box

Click to show or hide information. New Name

Type a new name for the user-defined type.


Expand all contracted text Show All

<Database Driver> Data Type dialog box

Click to show or hide information. Native type

Click the physical data type for the target DBMS to which the portable data type is mapped.

Click to show or hide information. Length

Type the number of characters.

Click to show or hide information. Precision

Type the total number of digits (the number of digits to the left of the decimal point plus the number of digits to the right of the decimal point).

Click to show or hide information. Scale

For applicable data types, type the number of digits that appear after the decimal point.

Click to show or hide information. Identity

Select this check box to indicate that the column contains system-generated values that uniquely identify each row within a table.

You can use this feature to generate sequential numbers (for example, employee IDs). When you insert values into a table that has an identity column, the database automatically generates the next identifier based on the last used identity value.


Create Index dialog box

Click to show or hide information. Non-unique index name

Type a name for this index. The name must be valid for the DBMS that is implementing this database structure.

By default, the table name is concatenated with the "_IDX" suffix (or whatever suffix was set in the Database Modeling Preferences window) to form an index name that changes to reflect any changes in the table name.

If you enter a different name, Microsoft Visio does not automatically update it if the table name changes.

To restore the default index name behavior, type ‘‘ (two single quotation marks).

Click to show or hide information. Unique constraint name

If the index has a uniqueness constraint, type its name.


Index Attributes dialog box

Click to show or hide information. Extended attributes

Lists the extended attributes for an index.

Click to show or hide information. Installed Visio drivers

Displays the default Visio database driver.


Microsoft SQL Server Extended Table Attributes dialog box

Click to show or hide information. Segment

Specifies the name for the segment of the host SQL database on which the index or table is to be stored.

The segment will be stored on the selected device in the Available Devices list.

Click to show or hide information. Available devices

Lists devices that have been created by the database administrator for the currently selected DBMS.

Click to show or hide information. Clustered PK

Specifies that the physical order of records in a table is the same as the order of the records when sorted by primary key.


Microsoft SQL Server Extended Index Attributes dialog box

Click to show or hide information. Segment

Specifies the name for the segment of the host SQL database on which the index or table is to be stored.

The segment will be stored on the selected device in the Available Devices list.

Click to show or hide information. Available devices

Lists devices that have been created by the database administrator for the currently selected DBMS.

Click to show or hide information. Unique index

Specifies that records must be unique—they cannot have the same index value. To define a column as a primary key, the index must be unique.

Click to show or hide information. Cluster index

The physical order of records in a table is the same as the indexed order of records.

Click to show or hide information. Ignore duplicate key

If you've checked Unique Index, specifies that each value in the indexed column is unique.

Click to show or hide information. Sort data

Sorts data in ascending order.

Click to show or hide information. Not allowed

If duplicate records exist in a table, an error message appears, and you cannot save the table until the error is corrected.

Click to show or hide information. Allowed

Allows duplicate records in the table, and creates the index anyway.

Click to show or hide information. Ignored

Specifies that as duplicate records are entered into the table, they are deleted.

Click to show or hide information. Fill factor

Specifies to use the database's default setting for how full each index page can be.

Click to show or hide information. Max row per page

Overrides the database's default setting for how full each index page can be, and creates a new records/page setting.


Code Editor dialog box (Body tab)

Type and edit SQL statements. To edit, use the toolbar buttons, or right-click the code to see additional options.


Code Editor dialog box (Properties tab)

Use these options to enter or revise the code component's name, choose the type of code it represents, and create a mirror file for saving the code component externally.

Click to show or hide information. Name

Type the name of the code that appears on the Body tab.

Click to show or hide information. Stored Proc

Click to specify that the code is a stored procedure.

Click to show or hide information. Function

Click to specify that the code is a function.

Click to show or hide information. Raw DDL

Click to specify that the code is raw DDL.

Click to show or hide information. View Definition

Click to specify that the code is a view definition.

Click to show or hide information. Trigger

Click to specify that the code is a trigger.

Click to show or hide information. Check Clause

Click to specify that the code is a check clause.

Click to show or hide information. File Name

Type the name of the mirror file to use to store your code.


Code Editor dialog box (Notes tab)

Click to show or hide information. List

Specifies the code comments that will be generated in the DDL script for the model.

Click to show or hide information. Generate As Comment In DDL

Specifies to include the code comments in the generated DDL script for the model.


Import ERwin ERX File dialog box

Imports PLATINUM ERwin 2.6, 3.0, and 3.52 .erx files and converts them to an IDEF1X data model.

The .erx format file is exportable from ERwin and contains information about a data model in text form.

The entities, attributes, relationships, categories, and text blocks are imported into a Microsoft Visio database model. ERwin domains are converted to portable data types when Visio has a database driver equivalent to an ERwin target server.

If Visio does not have an equivalent database driver, a warning message appears, and Visio maps the ERwin data type to a default data type instead.

Note Visio does not import the following ERwin features: stored procedures, custom trigger code, and ERwin displays.

Click to show or hide information. File name

Type the name of the file that you want to import.


Import VisioModeler IMD File dialog box

Click to show or hide information. File

Specifies the file you want to import.


IBM DB2 Universal Database dialog boxes

Consult your database driver's documentation for details about configuring options for this driver.


Oracle dialog boxes

Consult your database driver's documentation for details about configuring options for this driver.


Informix dialog boxes

Consult your database driver's documentation for details about configuring options for this driver.


OLE DB Provider Setup dialog boxes

Consult your database driver's documentation for details about configuring options for this driver.


Column, View Column, and Field Properties dialog boxes (Definition tab)

Use these options to specify attributes for a column in a table.

In addition to naming properties, you can determine whether null values are allowed for the selected column.

Click to show or hide information. Physical name

Type the name of the column as it appears in its table.

Click to show or hide information. Conceptual name

Type a name used for a column in the conceptual view of the model.

Click to show or hide information. Sync names when typing

Select this check box to cause the Physical Name or Conceptual Name box to be updated with the same name as you type in the other box.

Click to show or hide information. Default Value

Type a literal value or a calculated value (using an expression or function call).

Click to show or hide information. Is literal value

Click to indicate that the default is a literal value.

Click to show or hide information. Is an expression or function call

Click to indicate that the default is a calculated value.

Click to show or hide information. Allow NULL values

Select this check box to allow null values in this column.

If you clear this check box, any row that has no value for this column will cause validation to fail.


Column, View Column, and Field Properties dialog boxes (Data Type tab)

Use these options to specify how Microsoft Visio maps portable data types to platform-specific types used by your target DBMS.

You can display either portable or physical data types, and you can assign data types to columns.

Show Portable Data Type

Changes the options on the Data Type tab to display the portable data type.

These data types are automatically mapped to corresponding data types in your target DBMS.

Click to show or hide information. User defined

Click a user-defined data type. To define a new data type, on the Database menu, click User-Defined Types.

For object relational models, you can also click a composite type. To define a composite type, drag a Type shape from the Object Relational stencil to the drawing page.

Click to show or hide information. Category

Click a portable data type category available in your Visio product.

The data type definition you construct here automatically maps to the nearest native data type of your DBMS.

Click to show or hide information. Type

For the selected category, click an applicable portable data type.

Some values for Type also require Size, Length, Precision, or Scale values.

Click to show or hide information. Size

For the selected category and type, click a size.

Click to show or hide information. Length

Type the number of characters.

Click to show or hide information. Precision

Type the total number of digits (the number of digits to the left of the decimal point plus the number of digits to the right of the decimal point).

Click to show or hide information. Scale

Type the number of digits to the right of the decimal point.

Show Physical Data Type

Changes the options on the Data Type tab to display the native data type based on the default database driver.

Click to show or hide information. Data type

Displays the physical data type (native to the underlying DBMS) that the selected portable data type will map to. You can override this option on a local basis.

Click to show or hide information. Default driver

Displays the default driver. To change the default driver, on the Database menu, point to Options, and then click Drivers.


Column and Field Properties dialog boxes (Collection tab)

Use these options to specify whether the column is a single value or contains a collection of information.

Collections may be sets, lists, or multiple values, and may be used only in object-relational models.

Click to show or hide information. Unordered, non-repeating group of values (Set)

Click to allow population instances of a field in a column to have multiple values, but each value must be unique. The values do not have any sorting criteria; therefore, they are unordered.

Click to show or hide information. Ordered group of values (List)

Click to create an ordered collection of elements that allows duplicate elements. Each element in the collection has an ordinal position. An ordinal position is a position by which a value can be accessed. Because two values can be the same, they are differentiated by their ordinal positions.

Click to show or hide information. Unordered, repeating group of values (MultiSet)

Click to create a collection of elements that can have duplicate values. The elements do not have ordinal positions.

Click to show or hide information. Single value (Column)

Click to specify that there is no collection type property applied. This is the default type.


Column and Field Properties dialog box (Check tab)

Use these options to create check-clause code that enforces constraints for a column.

Microsoft Visio automatically generates the check-clause code based on a range or set of values that you specify on this tab. You can customize the automatically generated code.

Automatically generated check-clause code for a column does not appear in the Code window. If you then generate or update a DDL script for your target database, Visio adds the check clause to the Code window for that database.

Show Value/Range

Specifies the ranges or values that will be checked.

Click to show or hide information. From

Type the minimum value that defines the required range to check. If this is left blank, then no minimum value is assumed.

Click to show or hide information. To

Type the maximum value that defines the required range to check. If this is left blank, then no maximum value is assumed.

Click to show or hide information. Value

Type a value to include in a set of values. After each value, click Add.

Click to show or hide information. Add

Click to add the specified set or range of values to the Defined values/ranges box as a new check clause.

Click to show or hide information. Remove

Click to delete the check clause selected in the Defined values/ranges list.

Click to show or hide information. Defined values/ranges

Lists the specified ranges or values.

Show Check Clause Code

Click to show the column's check-clause code.

Click to show or hide information. Physical name

Shows the name of the check-clause code.

Click to show or hide information. Customize

Click to edit the check-clause code.

Click to show or hide information. Check Clause

Shows the check-clause code.


Column and Field Properties dialog box (Extended tab)

Click to show or hide information. Installed Visio drivers

Displays the default Visio database driver.

Click to show or hide information. Extended attributes

Displays the extended attributes of the column or field.

Click to show or hide information. Edit

If available, click this button to display a dialog box in which you can configure extended column attributes specific to the selected database driver.


Column, View Column, and Field Properties dialog box (Notes tab)

Click to show or hide information. Notes

Type notes. The notes you type here are included in the logical model.


Pick A Column dialog box

Click to show or hide information. List

Click a table, view, or column in the current model to include as a column in the selected view.


Microsoft Access dialog boxes

Consult your database driver's documentation for details about configuring options for this driver.


Microsoft SQL Server dialog boxes

Consult your database driver's documentation for details about configuring options for this driver.


Microsoft SQL Server 7.0 Extended Table Attributes dialog box

Click to show or hide information. Table On Group

Specifies the file group on which the table is stored. If Default is specified, or if On is not specified at all, the table is stored in the default file group.

Click to show or hide information. Text Image On

Indicates that the text, ntext, and image columns are stored on the specified file group. Text Image On is not allowed if there are no text, ntext, or image columns in the table.

Click to show or hide information. Clustered Primary Key

Specifies that the physical order of records in a table is the same as the order of the records when sorted by primary key.


Microsoft SQL Server 7.0 Extended Index Attributes dialog box

Click to show or hide information. Index On Filegroup

Specifies the filegroup that the index is placed on.

Click to show or hide information. Unique

Specifies that records must be unique—they cannot have the same index value.

Click to show or hide information. Clustered

Specifies that the physical order of records in a table is the same as the indexed order of records.

Click to show or hide information. Fill Factor

Specifies to use the database's default setting for how full each index page can be.

Click to show or hide information. IGNORE DUP_KEY

Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY is specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning message and ignores (does not insert) the duplicate row.

Click to show or hide information. PAD_INDEX

Specifies the space to leave open on each page (node) in the intermediate levels of the index. This option is useful only when the fill factor is specified.

Click to show or hide information. STATS_NORECOMPUTE

Specifies that statistics that become out of date are not automatically recomputed.

Click to show or hide information. DROP_EXISTING

Specifies that the named, preexisting clustered or nonclustered index should be dropped and the specified index rebuilt. The index name specified must be the same as a currently existing index.


Microsoft SQL Server 7.0 Database File Definition dialog box

Click to show or hide information. Logical File Name

Specifies a logical name for the database.

Click to show or hide information. Full Path Physical File Name

Specifies where the database file is physically stored on the server.

You must include the drive that your file is stored on. For example, if your database is on the E drive, type E:\ path.

Click to show or hide information. Initial Size

Allocates an initial file size for the database. Used for file and disk space management.

Click to show or hide information. Max Size

Specifies a maximum file size for the new database. Used for file and disk space management.

Click to show or hide information. MB/KB

Specifies whether the size you've entered in Initial Size and Max Size is in megabytes or kilobytes.

Click to show or hide information. File Growth Increments (In Kilobytes)

Specifies how much the database file can grow. Used for file and disk space management.


<Database Driver> Setup dialog box (ODBC Drivers tab)

Consult your database driver's documentation for details about configuring options for this driver.


<Database Driver> Setup dialog box (Preferred Settings tab)

Consult your database driver's documentation for details about configuring options for this driver.


Field Properties dialog box (Definition tab)

Click to show or hide information. Physical name

Type the name of the field.

Click to show or hide information. Allow NULL values

Select this check box to allow null values in this field.

If you clear this check box, any row that has no value for this field will cause validation to fail.


IBM DB2 Universal Database New Table Space dialog box

Click to show or hide information. Name

Type the name of the new table space.

Click to show or hide information. Nodegroup

Type the name of an existing nodegroup. The default name is IBMDEFAULTGROUP for regular and long table spaces and IBMTEMPGROUP for a temporary table space.

Click to show or hide information. Regular

Click to create a regular table space.

Click to show or hide information. Long

Click to create a long table space.

Click to show or hide information. Temporary

Click to create a temporary table space.

Click to show or hide information. System

Click to make the table space a System Managed Space (SMS).

Click to show or hide information. Database

Click to make the table space a Database Managed Space (DMS).

Click to show or hide information. Extent Size

Type the number of PAGESIZE pages written to a container before skipping to the next container.

Click to show or hide information. Using container string/clause

Type a complete USING container clause, including the USING keyword and all punctuation. The format of the container strings depends on the operating system.

For example, the following is a complete USING container clause for a System Managed Space on Windows NT:

USING ( ‘c:\my_tbsp', ‘d:\my_tbsp' )

The following is a complete USING container clause for a Database Managed Space on Windows NT:

USING ( FILE ‘c:\my_tbsp', ‘d:\my_tbsp' )

Click to show or hide information. Prefetch Size

Type the number of PAGESIZE pages read from the table space when data is being prefetched.

Click to show or hide information. Overhead

Type an integer, decimal, or floating point number to specify the I/O controller overhead, disk seek, and latency time, in milliseconds.

Click to show or hide information. Transfer Rate

Type an integer, decimal, or floating point number to specify the time required to read one page into memory, in milliseconds.

Click to show or hide information. Buffer Pool

Type the name of the buffer pool used for tables in this table space. The default name is IBMDEFAULTBP.


L1001

<Table name>: Conceptual name of table is not defined.

Explanation

Each table in a model must have a conceptual name.

Solution

Type a conceptual name for the table.


L1003

<Table name>: Physical name of table is not defined.

Explanation

Each table in a model must have a physical name.

Solution

Type a physical name for the table.


L1004

<Table name>: Physical name of table is not unique within its name space.

Explanation

Two tables in the same name space cannot have the same physical name.

Solution

Either type a different physical name for the table or assign the table to a different name space.


L1005

<Table name>: Table has no columns defined.

Explanation

Each table in a model must have at least one primary key column.

Solution

Add one or more columns to the table, and assign one of them to be a primary key.


L1007

<Table1>: Table has an illegal circular relationship with table <table2>.

Explanation

<Table1> is either directly or indirectly a parent of <table2> or vice-versa. Circular relationships are allowed only if one of the relationships involved is non-identifying.

Solution

Either make one of the relationships non-identifying or remove one of the relationships from the model.


L1009

<table1>: Circular relationship with table <table2> is not recommended.

Explanation

<Table1> is either directly or indirectly a parent of` <table2> or vice-versa, but one of the relationships is non-identifying, which makes the circular relationship illegal.

Solution

Remove one of the relationships from the model.


L1010

<Table name>: Conceptual name of table is not unique within its name space.

Explanation

Two tables in the same name space cannot have the same conceptual name.

Solution

Either type a different conceptual name for the table or assign the table to a different name space.


L1013

<Table name>: Multiple inheritance is not allowed between tables.

Explanation

One table cannot be directly derived from two or more other tables.

Solution

Remove one or more of the inheritance relationships.


L2001

<Column name>: Conceptual name of column is not defined.

Explanation

Each column in a table must have a conceptual name.

Solution

Type a conceptual name for the column.


L2003

<Column name>: Physical name of column is not defined.

Explanation

Each column in a table must have a physical name.

Solution

Type a physical name for the column.


L2004

<Column name>: Physical name of column is not unique.

Explanation

Two columns in the same table cannot have the same physical name.

Solution

Change the physical name of the column.


L2006

<Column name>: Primary key column that allows null values is not recommended.

Explanation

Primary keys are required and must contain unique identifying values. Null values generally indicate optional columns. Allowing a null value in a primary key is legal, but it is not recommended.

Solution

Either change the column attributes so that it does not allow null values, or remove the column from the list of primary keys for that table.


L2007

<Column name>: Conceptual name of column is the same as table <Table name> name.

Explanation

A column and a table cannot both have the same conceptual name, because they cannot represent the same conceptual object or entity. Two columns can share the same conceptual name if they represent the same conceptual object or entity.

Solution

Rename either the column or the table.


L2008

<Column1>: Column has same conceptual name as column <column2> but has a different data type.

Explanation

If two columns describe the same attribute and share the same conceptual name, they must also have the same data type.

Solution

Change the data type of the column.


L3001

<Relationship>: Parent column(s) not specified.

Explanation

A foreign key relationship was created, but either no parent columns were specified or one or more parent columns were not associated with their respective child columns.

Solution

Associate child columns with the correct parent columns.


L3002

<Relationship>: Child column(s) not specified.

Explanation

A foreign key relationship was created, but either no child columns were specified, or one or more child columns were not associated with their respective parent columns.

Solution

Associate child columns with the correct parent columns.


L3003

<Relationship>: Parent column(s) cannot be a subset of the primary key.

Explanation

The parent columns in this foreign key relationship must represent the entire primary key of the parent table.

Solution

Either modify the column associations for that relationship so that the parent columns represent the entire primary key, or remove the parent columns from the primary key.


L3004

<Column1>: Foreign key column data type not consistent with parent column <column2> data type.

Explanation

The data types of a child column and parent column in a foreign key relationship must be the same. Referential integrity cannot be guaranteed if the data types aren't the same.

Solution

Modify the data type of either column so that the data types are the same.


L3005

<Relationship>: The number of parent columns is different than the number of child columns.

Explanation

One or more parent columns in this foreign key relationship have no association with a child column.

Solution

Associate all parent columns with their child columns.


L3006

<Relationship>: Child column(s) <column1> allows null values that are not allowed in foreign key parent column(s) <column2>.

Explanation

Either the Allow NULL Values setting for child columns in a foreign key relationship must match those in the parent columns, or all child columns must allow null values. For example, suppose a parent table's primary key contains two columns, and neither allows null values. If one of these foreign key columns in the child table allows null values and the other does not, this error is generated.

Solution

Either change the child column's Allow NULL Values setting to match that of the parent column, or change all child columns to allow null values.


L3007

<Relationship>: Parent column <column1> allows null values but child column <column2> does not.

Explanation

The parent column is set to allow null values but the child column is not. Referential integrity cannot be guaranteed if the settings don't match.

Solution

Change the Allow NULL Values setting in the child column to match that of the parent column.


L3008

<Table name>: Ref. Integrity ‘Set NULL' option not valid when child column <column name> does not allow nulls.

Explanation

The On Update / On Delete Set NULL referential integrity option for this foreign key relationship contradicts the Allow NULL Values setting of a child column. Referential integrity cannot be guaranteed.

Solution

Either change the child column to allow null values, or change the referential integrity option for On Update / On Delete to something other than Set NULL.


L3009

<Relationship>: Referential integrity On Update Set Null option is invalid when the foreign key cardinality does not allow null values.

Explanation

The On Update Set NULL referential integrity option for this foreign key relationship contradicts the cardinality of the foreign key. Referential integrity cannot be guaranteed.

Solution

Change the cardinality of the foreign key.


L3010

<Relationship>: Referential integrity On Delete Set Null option is invalid when the foreign key cardinality does not allow null values.

Explanation

The On Delete Set NULL referential integrity option for this foreign key relationship contradicts the cardinality of the foreign key. Referential integrity cannot be guaranteed.

Solution

Change the cardinality of the foreign key.


L3011

<Relationship1>: Child columns overlap with child columns of <relationship2>.

Explanation

At least one child column of <relationship1> is also a child column of <relationship2>.

Solution

Either remove or redefine one of the foreign key relationships.


L3012

<Relationship>: Cardinality zero-or-one-to-one-or-more or zero-or-one-to-exactly-n is highly unusual.

Explanation

A foreign key relationship with a parent column that allows null values and a child column that does not allow null values is not recommended. Referential integrity cannot be guaranteed.

Solution

Change the cardinality of the foreign key relationship.


L3013

<Relationship>: Relationship has a cardinality of either zero-or-one or exactly-one, yet the child columns do not form a key.

Explanation

The cardinality of the relationship implies a uniqueness constraint over the child columns, yet there is no such constraint.

Solution

Add a uniqueness constraint to the child columns of the relationship by creating a unique index.


L3014

<Relationship1>: Foreign key relationship has same parent and child columns as <relationship2>.

Explanation

Two identical foreign key relationships exist; only one is necessary.

Solution

Remove one of the relationships.


L3015

<Relationship>: Category relationship cannot be non-identifying.

Explanation

The child entity in a category relationship must have a primary key that is identical to the primary key of the parent entity.

Solution

Either move the relationship attributes in the child entity to the primary key or change the category relationship to a non-identifying relationship.


L3016

<Column name>: Non-primary parent column in <relationship> must exist as an alternate key.

Explanation

Parent columns in a foreign key relationship must uniquely identify the parent table. If the parent columns are not the primary key, they must be an alternate key.

Solution

Either create an alternate key on the parent columns or move the offending column into the primary key area of the parent table.


L3017

Relationship has cardinality other than either zero-or-one or exactly-one, yet the child columns form a key.

Explanation

The cardinality of the relationship must be either zero-or-one or exactly-one, because the child columns are constrained to be unique. The cardinality of the relationship permits duplicate children, so the cardinality might be incorrect.

Solution

Change the cardinality of the relationship to either zero-or-one or exactly-one.


L3018

<Relationship>: Parent column <column1> and child column <column2> have mismatched value/range constraints.

Explanation

Two columns that are connected by a foreign key relationship must have the same set of allowable values. If they do not, referential integrity cannot be guaranteed.

Solution

In the Column Properties dialog box, click the Check tab, and then change either column's value or range configuration.


L3019

Relationship has a cardinality of either zero-or-one or exactly-one, yet the child columns form part of a key.

Explanation

The relationship targets part of a key and has a cardinality of zero-or-one or exactly-one. If this is the case, either the child columns form the true key and the other columns in the key are redundant, or the cardinality of the relationship is incorrect.

Solution

Change the cardinality of the relationship.


L3020

<Relationship1>: Foreign key relationship has same parent and child tables as <relationship2> but does not have different forward and inverse verb phrases.

Explanation

Two relationships between the same two tables that are described by the same forward and inverse verb phrases cannot be uniquely identified.

Solution

Either change the verb phrase of one of the relationships or remove one of the relationships.


L3021

<Relationship>: Category relationship must be from the parent primary key to the child primary key and both primary keys must be the same size.

Explanation

A category relationship represents an inheritance relationship between a parent entity and a child entity. A child entity in a category relationship must inherit the same primary key as that of its parent.

Solution

Either move the appropriate columns in the child entity to the primary key or change the category relationship to a non-identifying relationship.


L3022

<Relationship>: Self-referencing foreign key column <Column name> cannot exist as both a parent and a child in the same relationship.

Explanation

A self-referencing foreign key relationship contains a column listed as both a parent column and as a child column. This listing is invalid.

Solution

Change the relationship definition.


L3023

<Relationship>: Foreign key relationship contains a duplicate child column <Column name>.

Explanation

A foreign key relationship cannot contain a child column that is associated with more than one parent column in the same relationship.

Solution

Change or remove the association between the child column and the respective parent column.


L3024

<Relationship>: Foreign key relationship contains a duplicate parent column <Column name>.

Explanation

A foreign key relationship cannot contain a parent column that is associated with more than one child column in the same relationship.

Solution

Change or remove the association between the child column and the respective parent column.


L3025

<Column name>: Child column in foreign key <foreign key name> cannot be a subfield of a composite type.

Explanation

A child column in a foreign key relationship is a subfield of a domain type, distinct type, or row type. Composite types inside keys or foreign key relationships are not supported.

Solution

Remove the column from the foreign key relationship.


L3026

<Column name>: Parent column in foreign key <foreign key name> cannot be a subfield of a composite type.

Explanation

A parent column in a foreign key relationship is a subfield of a domain type, distinct type, or row type. Composite types inside keys or foreign key relationships are not supported.

Solution

Remove the column from the foreign key relationship.


L3027

Foreign key relationship cardinality minimum is greater than the maximum.

Explanation

The relationship cardinality can never be satisfied if the minimum cardinality exceeds the maximum cardinality. For example, no number can be more than 17 and less than 15.

Solution

Change the minimum and maximum cardinality.


L4004

<Index>: Alternate key (index) on table <Table name> cannot be the same as or a part of the primary key.

Explanation

The columns of an alternate key cannot be contained in the primary key.

Solution

Either remove the alternate key or add one or more non-primary columns to the alternate key's column list.


L4005

<Index1>: Alternate key on table <Table name> and alternate key <index2> (on same table) are functionally identical.

Explanation

<Index1> contains a superset of <index2>‘s columns, making it functionally similar to but less optimal than <index2>.

Solution

Either remove <index1> or modify one of the redundant indexes so that <index2> is no longer a subset of <index1>.


L4007

<Column name>: Primary key on table <table name> cannot contain subfields of a composite type.

Explanation

A column in a primary key is a subfield of a domain type, distinct type, or row type. Composite types inside keys or foreign key relationships are not supported.

Solution

Remove the column from the primary key.


L4008

<Column name>: Index on table <table name> is not allowed to contain subfields of a composite type.

Explanation

A column in an index is a subfield of a domain type, distinct type, or row type. Composite types inside keys or foreign key relationships are not supported.

Solution

Remove the column from the index.


L5001

<Table name>: Table has category that does not have a discriminator.

Explanation

Category relationships are meaningless without a discriminating attribute that distinguishes one subcategory from another.

Solution

Select a discriminator attribute on the parent table.


L5002

<Table name>: Category is marked as complete, but has only one subcategory.

Explanation

Complete category relationships usually involve more than one subcategory.

Solution

Either set the category relationship as incomplete or add more child tables.


L6006

<Type>: Type inheritance can only exist between named row types.

Explanation

A type inheritance relationship exists between two types, and one of the types is a domain type or a distinct type. Type inheritance can exist only between two named row types.

Solution

Either delete the type inheritance relationship, or recreate the domain type or distinct type as a named row type.


L6007

<Field>: Single row type field allows null values.

Explanation

A row type consists of only one field that allows null values. This circumstance is not recommended.

Solution

Change the field so that it does not allow null values.


L7000

<Field>: Physical name of field is not unique.

Explanation

Two fields in a row type cannot share the same physical name.

Solution

Either remove or rename the duplicate field.


M1010

<Type>: User-defined types from multiple source documents conflict.

Explanation

User-defined types must be consistent among documents.

Solution

Make user-defined types identical.


M2004

Cannot open model.

Explanation

At least one model needed to build this project is unavailable. It may be open in another program or in another instance of your Microsoft Visio product.

Solution

Close the other instance of the model(s).


P1001

<Table name>: Physical name is a reserved word in the target database management system.

Explanation

The table name conflicts with a reserved word in the database management system. Examples of reserved words include Select, Table, and Create. Check the documentation of the target database management system for a list of reserved words.

Solution

Choose a different table name.


P1005

<Table name>: Row size in the table exceeds the maximum size (<number>) allowed by the target database management system.

Explanation

The total of all the column lengths in the table exceeds the maximum size allowed for a table as a whole. Check the documentation of the target database management system for specific limits.

Solution

Adjust the table row size by changing the length of the data type in individual columns.


P1006

<Table name>: Too many indexes defined in the table. Maximum allowed is <number> for the target database management system.

Explanation

The number of indexes defined on the table exceeds the maximum number allowed for the target database management system. Check the documentation of the target database management system for specific limits.

Solution

Change the number of indexes to the number allowed by the target database management system.


P1007

<Table name.column name>: Column exceeds the maximum characters (<number>) allowed by the target database management system.

Explanation

The column name exceeds the maximum column name length allowed by the target database management system. Check the documentation of the target database management system for specific limits.

Solution

Change the column name length to the limit allowed by the target database management system.


P1008

<Table name.column name>: Column name is a reserved word in the target database management system.

Explanation

The column name conflicts with a reserved word in the database management system. Examples of reserved words include Select, Table, and Create. Check the documentation of the target database management system for a list of reserved words.

Solution

Choose a different column name.


P1009

<Table name.column name>: Column name contains invalid character(s).

Explanation

The column name contains characters not allowed in the target database management system. Examples of invalid characters include #, $, and @. Check the documentation of the target database management system for a list of invalid characters.

Solution

Remove the invalid character in the column name and replace it with a valid character.


P1010

<Table name.column name>: Column data type length exceeds the maximum (<number>) allowed by the target database management system.

Explanation

The column length exceeds the maximum allowed by the target database management system. For example, some database management systems have a limit of 255 characters for a character data type. Check the documentation of the target database management system for specific limits.

Solution

Change the data type length associated with the column to conform to the target database management system limit.


P1011

<Table name>: Table has an index with no defined index name.

Explanation

An index exists on the table, but no index name has been defined.

Solution

Define an index name for the index.


P1012

<Table name>: Table has a foreign key with no defined foreign key name.

Explanation

A foreign key exists on the table, but no foreign key name has been defined.

Solution

Define a physical name for the foreign key.


P1013

<Table name>: Table has a primary key with no defined primary key name.

Explanation

A primary key exists on the table, but no primary key name has been defined.

Solution

Define a physical name for the primary key.


P1014

<Index name>: Index name in the table <table name> exceeds the maximum number of characters (<number>) allowed by the target database management system.

Explanation

The index name exceeds the maximum index name length allowed by the target database management system. Check the documentation of the target database management system to determine specific limits.

Solution

Shorten the index name length.


P1015

<Table name>: Foreign key name in the table <table name> is a reserved word in the target database management system.

Explanation

The foreign key name conflicts with a reserved word in the database management system. Examples of reserved words include Select, Table, and Create. Check the documentation of the target database management system for a list of reserved words.

Solution

Choose a different foreign key name.


P1017

<Table name>: Primary key name in the table <table name> exceeds the maximum primary key name length (<number>) allowed by the target database management system.

Explanation

The primary key name exceeds the maximum length allowed by the target database management system. For example, a database management system might have a limit of 30 characters for a primary key name. Check the documentation of the target database management system to determine specific limits.

Solution

Shorten the primary key name length.


P1019

<Table name>: Primary key name in the table <table name> is a reserved word in the target database management system.

Explanation

The primary key name conflicts with a reserved word in the database management system. Examples of reserved words include Select, Table, and Create. Check the documentation of the target database management system for a list of reserved words.

Solution

Choose a different primary key name.


P1021

<Table name>: Primary key name in the table <table name> contains invalid character(s).

Explanation

The target database management system does not allow certain characters in the primary key name. Examples of invalid characters include #, $, and @. Check the documentation of the target database management system for a list of invalid characters.

Solution

Remove the invalid character in the primary key name and replace it with a valid character.


P1022

<Table name>: Index name in the table <table name> contains invalid character(s).

Explanation

The target database management system does not allow certain characters in the index name. Examples of invalid characters include #, $, and @. Check the documentation of the target database management system for a list of invalid characters.

Solution

Remove the invalid character in the index name and replace it with a valid character.


P1023

<Table name.column name>: Column is of data type <data type>. Data type <data type> cannot have an index on the target database management system.

Explanation

The target database management system does not allow the selected data type to have an index. For example, the Boolean (true/false) data type cannot have an index. Check the documentation of the target database management system for details about data types that support indexes.

Solution

Either remove the index from the column or change the data type of the column to one that allows an index.


P1024

<Table name.column name>: Primary key columns that allow null values are not allowed in the target database management system.

Explanation

A primary key helps ensure that there are no duplicates in a table. If null values can exist in the primary key, duplicate null fields might exist. The target database management system does not allow primary keys to be declared as null values.

Solution

Uncheck Allow Null Values for the primary key column.


P1037

<Column name>: Composite type columns are not supported by the target database management system.

Explanation

The target database management system does not support composite types.

Solution

Change the data type for the column.


P1025

<Table name>: Index size in the table exceeds the maximum size (<number>) allowed by the target database management system.

Explanation

The total length of all the columns in the index exceeds the maximum size allowed by the target database management system. Check the documentation of the target database management system for specific limits.

Solution

Change the columns in the index.


P1026

<Table name.column name>: No precision specified for column's data type.

Explanation

Some data types, such as DECIMAL and MONEY, require a precision value.

Solution

Specify the precision for the data type selected for the column.


P1027

<Table name.column name>: Data type <data type> has Scale value that is longer than the Precision value.

Explanation

Scale is the number of digits to the right of the decimal point. The Scale value cannot be greater than the Precision value for this data type.

Solution

Either change the Scale value for the data type to a number that matches or is less than the Precision value, or change the Precision value to match the Scale value.


P1028

<Table name.column name>: Column with data type <datatype name> or column with identity attribute cannot be nullable in the target DBMS.

Explanation

The target database management system does not allow the selected data type to have null values. Data types that commonly cannot have null values include bit fields, counter fields, and true/false fields. Check the documentation of the target database management system for details about data types that support null values.

Solution

Change the column so that it does not allow null values.


P1029

<Table name>: Only one column per table is allowed to have the TIMESTAMP data type.

Explanation

Only one column per table is allowed to have the TIMESTAMP data type.

Solution

Change the data type for the second column that uses the TIMESTAMP data type.


P1052

<Table name>: The user-defined type name exceeds the maximum number of characters (<number>) allowed by the target database management system.

Explanation

The user-defined type name exceeds the maximum name length allowed by the target database management system. Check the documentation of the target database management system to determine specific limits.

Solution

Rename the user-defined type.


P1030

<Type>: The user-defined type name is a reserved word in the target database management system.

Explanation

The user-defined type name conflicts with a reserved word in the database management system. Examples of reserved words include Select, Table, and Create. Check the documentation of the target database management system for a list of reserved words.

Solution

Rename the user-defined type.


P1031

<Type>: The first character in the user-defined type name is invalid.

Explanation

The target database management system does not allow certain characters to be the first character of a user-defined type name. Examples of invalid characters include #, $, and @. Check the documentation of the target database management system for a list of invalid characters.

Solution

Rename the user-defined type to remove the invalid character and replace it with a valid character.


P1032

<Type>: The user-defined type name contains invalid character(s).

Explanation

The user-defined type name contains characters that are invalid. Examples of invalid characters include #, $, and @. Check the documentation of the target database management system for a list of invalid characters.

Solution

Rename the user-defined type.


P1033

<Index name>: Index name is not unique.

Explanation

The target database management system prohibits duplicate index names.

Solution

Rename the indexes so that each has a unique name.


P1035

<Table name.column name>: Nullable unique constraint columns are not allowed in the target DBMS.

Explanation

The target database management system does not permit a unique constraint column to allow null values.

Solution

Remove the unique constraint from the columns that allow null values, and remove any foreign keys that target the columns.


P1036

<Column name>: Collection types are not supported by the targeted database management system.

Explanation

The target database management system does not support collection types.

Solution

Change the collection type setting for the column to Single Value (Column).


P1050

<Table name.column name>: View Column must be based on another table/view column or a user defined derivation rule.

Explanation

A view column must either be defined to be based on a column in another view or table or must have derivation rule defined.

Solution

Edit the view column and specify a column in another view or table or specify a derivation rule.


P1701

<Table name.column name>: Non-nullable column added to the table will be set with default value 1.

Explanation

Your Microsoft Visio product will attempt to move data from the current version of the table to the new version of the table. The default value of the column is set to one because the column doesn't allow null values.


P1702

<Table name>: This table contains columns with the LONG data type, whose data cannot be preserved through the change table process.

Explanation

The changes you have made to the table require dropping the table and recreating it with the modified definition. However, the table contains a column that has a LONG data type (a data type that allows the data to be arbitrarily long and possibly greater than 64K). Columns of such type cannot be used in a "Insert into ... Select " statement.

Solution

Back up the table containing the columns that have LONG data types so that the columns can be repopulated.


P1703

<Table name.column name>: The datatype for the column has been changed from <datatype name1> to <datatype name2>. The data may not be migrated in some cases

Explanation

You have specified that the data type for a column in the database should be changed. Some data type conversions result in information being lost. For example, information is lost when you convert a string data type such as "This is a long string" to a numeric data type, or when you convert a character to an integer. If you specify such a change and your Char field has characters in it, information will be lost.

If the target database management system supports the data type conversion, your Microsoft Visio product will use it and convert the data. If the target database management system does not support the data type conversion, your Visio product will leave the original data in a backup table for you to access and convert separately.

Solution

Back up your database before you make any schema changes. Before you change any data types in an existing schema, determine whether a data type conversion is possible in the target database management system, and make sure the data that exists in the database at the time of conversion will be converted as you specify.


P1704

<Table name>: You will not be able add a primary key or unique index on a newly added column if the table contains data.

Explanation

If a table already contains data, it might contain duplicate rows. Because a primary key and unique indexes require all rows to be unique, the database management system cannot enforce uniqueness.

Solution

If you are sure that the data in the table does not contain any duplicate values in the column(s) for which you have the primary key or unique index, ignore this warning.

If you aren't sure, follow these steps:

  1. Copy all the data to a temporary table.
  2. Delete the data from the original table.
  3. Create the primary key or unique index.
  4. Copy the data back from the temporary table and allow the database management system to enforce uniqueness.


P2000

<Table name>: <number> clustered indexes defined.

Explanation

Only one clustered index is allowed per table. A clustered index determines the physical layout of data within the storage medium, and there can be only one criterion by which to order the data. The target database management system allows only one clustered index per table.

Solution

Turn off clustering for all but one of the table's indexes.


P2100

<Table name>: Descending index is not supported in Sybase SQL Anywhere. The index will be created in ascending collation sequence.

Explanation

A descending index has been specified in the model but is not supported in the target database management system. The index will be created in an ascending collation sequence.

Solution

Check the model and implementation requirements. If you do not want to see this warning again, change the index in the model to ascending.


P2200

<Table name>: Index cannot be created with a descending order. The index will be created in ascending collation sequence.

Explanation

A descending index has been specified in the model but is not supported in the target database management system. The index will be created in an ascending collation sequence.

Solution

Change the index in the model to ascending.


P2300

<Table name>: The target database management system does not allow mixing of ascending and descending collation sequences when creating an index.

Explanation

Having both the ASC and the DESC collation sequence in the target database management system is not allowed. For example, if there are two columns, first name and last name, specified for an index, the target database management system does not allow an ASC collation sequence on the first name column and a DESC on the last name column. The index will be created based on the collation sequence defined for the first column.

Solution

Change the collation sequences so that they match for all of the columns in the index.


P2400

<Column name>: A table can contain only one column of data type LONG and column <Column name> has already been defined as LONG.

Explanation

The target database management system only allows one column to be of type LONG.

Solution

Change the column data type to some type other than LONG.


P2401

<Column name>: No storage table(s) defined for nested table type column in the table.

Explanation

Oracle requires that a storage table must be defined for a nested table type column.

Solution

Define a storage table by assigning extended attributes to the table in your model.


P2402

<Column name>: Composite or collection types cannot be used with the LOB data types (BLOB, CLOB, NCLOB).

Explanation

Oracle8 does not support the use of composite or collection types on any of the LOB (CLOB, NCLOB, and BLOB) data types.

Solution

Either remove the composite or collection type with LOB data types or change the LOB data types to a different data type.


P2403

<Type>: Collection type cannot be based on another collection type or object type which contains collection type attributes.

Explanation

Oracle does not support the nesting of collection types.

Solution

Remove the nested collection type.


P2404

<Type>: No storage table(s) defined for nested table type attributes in the typed table.

Explanation

Oracle requires that a storage table must be defined for a nested table type column.

Solution

Define a storage table by assigning extended attributes to the table in your model.


P2405

<Column name>: Distinct or unnamed row type column is not supported in Oracle8.

Explanation

Oracle does not support distinct or unnamed row types.

Solution

Remove the distinct or unnamed row type.


P2406

<Table name>: Index-organized table needs to have a primary key defined.

Explanation

Oracle requires a primary key to be defined on a table that is index-organized.

Solution

Define a primary key for the columns associated with one of the indexes.

How do I edit or create a primary key?

How do I create an index?


P3000

<Table name.column name>: Precision for numeric() and decimal() identity is too long. The maximum allowed is <length>.

Explanation

The column's data type length exceeds the maximum length.

Solution

Shorten the column's data type length.


P3001

<Column name>: Only one column is allowed to have the ROWGUIDCOL attribute defined.

Explanation

Microsoft SQL Server doesn't support more than one column with the ROWGUIDCOL attribute.

Solution

Change the data type for all but one of the columns in a table that have the ROWGUIDCOL attribute defined.


P3600

<Table name>: Mismatched datatypes between child column <Table name.column name1> and parent column <table name.column name2> are not allowed (only LONG/COUNTER is allowed) in the foreign key relationship under Microsoft Access.

Explanation

Parent and child column data types must match. In Microsoft Access, a foreign key field data type that points to a counter field data type must be of data type LONG.

Solution

Change the parent and child column data types to match or to be LONG/COUNTER.


P3700

<Table name.column name>: Set nullable option is not supported in the referential integrity constraint in the Microsoft Access DBMS.

Explanation

There is no way to support a Set Null referential integrity constraint in Microsoft Access.

Solution

Change the referential integrity settings for the foreign key relationship.


P4000

<Table name>: <Number> clustered indexes defined. Only one clustered index allowed per table.

Explanation

INFORMIX-Universal Server allows you to define only one clustered index per table. Because clustered indexing involves the physical layout of data within the storage medium, there can be only one criterion by which to order the data.

Solution

Turn off clustering for all but one of the table's indexes.


P4001

<Table name.column name>: Column length exceeds the maximum (<length>) allowed by the DBMS.

Explanation

The column length exceeds the maximum length allowed by the target database management system. For example, some database management systems have a limit of 255 characters for a character data type.

Solution

Change the data type length associated with the column to conform to the target database management system's limits.


P4002

<Table name>: Mismatched datatypes, precision or scale between child column <table name.column name1> and parent column <table name.column name2> are not allowed in the foreignkey relationship under Informix DBMS.

Explanation

Parent and child column data types must match. Some aspect of the data type, such as the data type, the precision of the data type, or the scale, does not match between the parent and child column.

Solution

Make sure the data type, precision, and scale of both the parent and child columns match.


P4003

<Index name>: Index constraint <constraint name1> cannot be created because it has the same columns as the child columns of the foreign key constraint <constraint name2>.

Explanation

With INFORMIX-Universal Server, if any columns in a unique index are the same as the child columns of an FK relationship in the table, creation of the index will fail.

Solution

Remove the index or change the order of the columns in the index.


P4004

<Table name.column name>: Invalid column name. Reserved word.

Explanation

The target database management system does not allow you to use reserved words, such as From, as column names.

Solution

Change the column name.


P4200

<Foreign key name>: Mismatched datatype, precision or scale between child column <table name.column name1> and parent column <table name.column name2> are not allowed in the foreignkey relationship under Adaptive Server Anywhere DBMS.

Explanation

Parent and child column data types must match. Some aspect of the data type, such as the data type, precision, or scale, does not match.

Solution

Check the data type, precision, and scale for the parent and child columns, and change them to match.


P4201

<Column name>: Sybase SQL Anywhere does not support columns in a primary key or unique index set to allow null values.

Explanation

Sybase SQL Anywhere does not permit you to create primary keys and unique indexes based on columns that allow null values.

Solution

Either uncheck Allow Null Values, or remove the column from the primary key or from the unique index.


P4400

<Table name>: A primary key is required if you have an index defined in the table.

Explanation

The Corel Paradox database management system requires a primary key for tables with an index.

Solution

Define a primary key for the table on which an index is defined.


P4401

<Table name>: An index is required for the source (parent) column in the foreign key relationship.

Explanation

The Corel Paradox database management system requires an index on parent columns involved in foreign key relationships.

Solution

Define an index for the parent column.


P4402

<Foreign key name>: Mismatched data types, precision, or scale between child column <table name.column name1> and parent column <table name.column name2> are not allowed in the foreign key relationship under Corel Paradox.

Explanation

The Corel Paradox database management system requires that the data type, precision, and scale match between the child and parent columns in a foreign key relationship.

Solution

Change the parent and child column data types to match.


P4450

<Table name>: The Corel Paradox ODBC driver does not allow table names over 8 bytes long.

Explanation

The Corel Paradox ODBC driver has an internal 8-character limit on table names. The Corel Paradox database management system does not have this limitation.

Solution

Either switch to a different ODBC driver, or generate a DDL script and then run it from within the Corel Paradox database management system.


P4600

<Key name>: Primary key cannot be defined on a row type.

Explanation

INFORMIX-Universal Server requires that columns involved in an index be built-in types. Because a primary key will create an index, no column in a primary key can contain a row type.

Solution

Either change the primary key to map to no key or change the primary key to a different identifier.


P4601

<Index name>: Index cannot be defined on a row type.

Explanation

INFORMIX-Universal Server requires that columns involved in an index be built-in types.

Solution

Remove the index.


P4602

<Table name>: Table must be typed in order to participate in an inheritance relationship.

Explanation

The implementation requires that tables using inheritance be typed tables.

Note Tables containing columns cannot be typed.

Solution

Base the table on a type by selecting a defining type.


P4603

<Table name>: Table inheritance between table <table name> and table <table name> requires associated types to have the same inheritance relationship.

Explanation

The implementation requires that tables using inheritance follow the inheritance patterns of their types.

Solution

Base the table on the type that has the inheritance relationship that you want.


P4607

<Table name.field name> Aggregate Field Ref's are not supported in the Informix Universal Server.

Explanation

INFORMIX-Universal Server does not support reference types.

Solution

Use foreign keys or row IDs instead.


P4608

<Foreign key name>: Foreign key relationship must be defined on columns with matching datatype, precision, and scale. Parent columns of type SERIAL (SERIAL8) match with child columns of type INTEGER (INT8).

Explanation

The parent and child column data types must match. Some aspect of the data type, such as the data type, the precision of the data type, or the scale, does not match.

Note A parent column of type SERIAL (SERIAL8) might have a child column of type INTEGER (INTEGER8).

Solution

Change the parent and child column to have the same data type, precision, and scale.


P4609

<Distinct type name>: You cannot create a distinct type based on LVARCHAR, SENDRECV, and IMPEXP primitive data types.

Explanation

A distinct type in INFORMIX-Universal Server cannot be based on the types LVARCHAR, SENDRECV, or IMPEX.

Solution

Use the data type itself instead.


P4700

<Identifier> is a reserved word and may result in syntactic ambiguities in SQL statements.

Explanation

INFORMIX-Universal Server does not allow you to use reserved words, such as From, in names.

Solution

Change the name to a word that is not reserved.


P4800

<Table name.column name>: The length of datatype <datatype name> is <length1>. It exceeds the maximum length of <length2>.

Explanation

The BLOB data type size exceeds the maximum permitted by the IBM DB2 database management system.

Solution

Decrease the BLOB data type size.


P4801

<Column name>: Parent column of the foreign key constraint is not in the primary key column list.

Explanation

The IBM DB2 database management system does not permit the target of a foreign key relationship to be an alternate key. The target of the relationship must be in the primary key.

Solution

Either mark the parent column as the primary key in the parent table or enforce the intended rule with a trigger or stored procedure.


P4802

<Table name>: Table inheritance is not supported in IBM DB2 Server.

Explanation

IBM DB2 Server does not support the ability to define a parent-child relationship between two separate tables.

Solution

Delete the table inheritance relationship and redefine it as a foreign key relationship between the primary keys of the two tables.


P4803

<Table name.column name>: <Datatype name> type columns not based on a built-in type are not supported in the CREATE TABLE statement.

Explanation

The IBM DB2 database management system does not support composite types that are not based on built-in types.

Solution

Either base the composite type on a built-in type or remove it.


P4804

<Table name>: Unnamed row type column is not supported in the CREATE TABLE statement.

Explanation

The IBM DB2 database management system does not support unnamed row types.

Solution

Remove the unnamed row type.


P4805

<View>: WITH CHECK OPTION keyword cannot be part of the CREATE VIEW statement when it involves more than one table/view.

Explanation

The IBM DB2 database management system does not support the WITH CHECK OPTION keyword as part of the CREATE VIEW statement when it involves more than one table or view.

Solution

Either remove the WITH CHECK OPTION keyword from the CREATE VIEW statement or remove the additional tables or views.


P4806

<Table name>: Table with referenced foreign keys cannot be created when not logged initially.

Explanation

The IBM DB2 database management system does not support the creation of tables with referenced foreign keys that are not initially logged.

Solution

Uncheck the Not Logged Initially check box in the IBM DB2 Universal Database Extended Table Attributes table.


P4850

<Column name>: Referential integrity for On Update Cascade and On Update Set Null are not supported in IBM DB2 Server. No Action will be substituted.

Explanation

IBM DB2 Server does not support Cascade or Set NULL as choices for the On Update rule.

Solution

Either change referential integrity settings or enforce the intended rule with a trigger or stored procedure.


P4851

<Type>: Row types, domain types, unnamed row types, and distinct types not based on a built-in data type are not supported in the IBM DB2 database management system. No DDL will be generated.

Explanation

The IBM DB2 database management system only permits complex types to alias a single built-in data type. No DDL will be generated for the invalid type.

Solution

Redefine the type to be based on a single built-in data type.


P4852

<Type>: Type inheritance is not supported in the IBM DB2 database management system. No DDL will be generated.

Explanation

The IBM DB2 database management system does not support the ability to define a parent-child relationship between two separate types.

Solution

Delete the type inheritance relationship.


L3028

<Column name>: The 'Set default' referential action option is not valid when child column <column name> does not have a default value or allow nulls.

Explanation

This is caused when the foreign key's referential action is set to ‘Set default' and the column on the child side of the foreign key is neither nullable nor has default values.

Solution

Select a different referential action option.


L2201

<Column name>: The default value is incompatible with the data type or is too long.

Explanation

This is caused when the default value for a column is incompatible with the column's data type (such as a default text string for a column with a numeric data type or a default text string that is longer than the length specified for the column).

Solution

Specify a different default value.


P1054

<Column name>: The default value is not a valid expression or function call for the target DBMS.

Explanation

If the default value for a column is an expression, the default value is not a supported expression or function call for the database driver.

Solution

Specify a different default value.


P1055

<Column name>: The default value is not a valid literal or not allowed with the datatype for the target DBMS.

Explanation

The literal value given as the default value for the column is not supported for the data type chosen for the column.

Solution

Specify a different default value.


P1056

<Column name>: A default value is not allowed for a column defined with an Identity attribute for the target DBMS.

Explanation

An Identity attribute for a column does not allow default values.

Solution

Either clear the Identity attribute or clear the default value.


L1008

<Table name>: Derived table is not allowed to have a primary key. The primary key is inherited from the base table.

Explanation

A table derived from another table inherits the primary key of the base table and cannot have a separate primary key.

Solution

Remove the primary key columns from the derived table.


L4001

<Index1>: Index on table <Table name> and index <index2> on the same table are identical.

Explanation

Two indexes on this table are identical.

Note Some database management systems create indexes automatically but hide them from the user. For example, Microsoft Access creates an index for every foreign key, but the index is not visible to the user.

Solution

Remove one of the indexes.


L4002

<Index>: Index on table <Table name> has no columns defined.

Explanation

An index was created on this table, but no columns were inserted into the index.

Solution

Either remove the index or add columns to it.


L4003

<Index>: Alternate key (index) on table <Table name> is identical to primary key.

Explanation

An alternate key cannot be identical to a primary key.

Solution

Either remove the alternate key or modify its column list so that it is no longer identical to that of the primary key.


L4006

<Table name>: Primary key on table <table name> cannot be a subset of an alternate key.

Explanation

A primary key cannot be wholly contained within an alternate key. For example, if you define the primary key of the table Person to be ssn, you cannot then define an alternate key of ssn and lname.

Solution

Make sure that the primary key is not wholly contained inside an alternate key.


L5003

<Table name>: Table has duplicate category with discriminator <Column name>.

Explanation

Two categories use the same discriminator attribute and therefore are redundant.

Solution

Remove one of the categories, and then merge its subcategory entities with the remaining category.


L6000

<Distinct type>: Distinct type references an alias that is more than one type.

Explanation

Distinct types can only reference an alias of one type.

Solution

Either remove the invalid distinct type or change it to reference an alias that is a single named type.


L6001

<Distinct type>: Distinct type references an alias that is an unnamed row type.

Explanation

Distinct types can only reference an alias that is a named type.

Solution

Either remove the invalid distinct type or change it to reference an alias that is a single named type.


L6002

<Domain>: Domain type must reference an alias that is a built-in type, collection type, or unnamed row type.

Explanation

A domain type can only reference an alias that is a built-in type, collection type, or an unnamed row type.

Solution

Either remove the invalid domain type or select a new aliased type.


L6003

<Named row type>: Named row type has no defined fields.

Explanation

A named row type must contain at least one field.

Solution

Either insert one or more fields into the type or remove the type.


L6004

<Unnamed row type>: Unnamed row type has no defined fields.

Explanation

A named row type must contain at least one field.

Solution

Either insert a field into the type or remove the type.


L6008

<Type>: Aliased type cannot allow null values.

Explanation

A domain type or distinct type references an alias of a type that allows null values. This circumstance is not recommended.

Solution

Uncheck Allow Null Values for the aliased type from where it is aliased.


L7001

<Type>: A composite type originating from a logical document cannot be associated with objects built from different documents.

Explanation

A row type, distinct type, or domain type was created in a logical model in a project and then referenced in another type or table that originated from another source document. Cross-document references of types are not supported.

Solution

Make sure that the two connected objects originate from the same document by cutting and pasting one of them into the other document.


M1011

Subtype has conflicting primary supertype.

Explanation

An object type that is a subtype can have only one primary supertype.

Solution

Make one of the subtype/supertype relationships non-primary.


P1000

<Type name>|<table name>: Table name exceeds the maximum number of characters.

Explanation

The type or physical name exceeds the number of characters allowed by the target database management system.

Solution

Shorten the type or physical name.


P1002

<Table name>: First character in the table name is not valid for the target database management system.

Explanation

The first character in the table name is not allowed in the target database management system. Examples of invalid characters include #, $, and @. Check the documentation of the target database management system for a list of invalid initial characters.

Solution

Remove the invalid character that begins the table name and replace it with a valid character.


P1003

<Table name>: Table name contains characters that are invalid for the target database management system.

Explanation

The table name contains characters not allowed in the target database management system. Examples of invalid characters include #, $, and @. Check the documentation of the target database management system for a list of invalid characters.

Solution

Remove the invalid character in the table name and replace it with a valid character.


P1004

<Table name>: Too many columns defined in the table for the target database management system.

Explanation

The target database management system allows only a specific number of columns to be defined for a table. Check the documentation of the target database management system for specific limits.

Solution

Change the number of columns to the number allowed by the target database management system.


P1016

<Table name>: Foreign key name in the table <table name> exceeds the maximum number of characters (<number>) allowed by the target database management system.

Explanation

The foreign key name exceeds the maximum length allowed by the target database management system. For example, some database management systems have a limit of 255 characters for a character data type. Check the documentation of the target database management system for specific limits.

Solution

Change the foreign key name length to conform to the target database management system limit.


P1020

<Table name>: Foreign key in the table <table name> contains invalid character(s).

Explanation

The target database management system does not allow certain characters in the foreign key name. Examples of invalid characters include #, $, and @. Check the documentation of the target database management system for a list of invalid characters.

Solution

Remove the invalid character in the foreign key name and replace it with a valid character.


C1003

<Object type name>: Object type has no primary reference scheme, but other identifying uniqueness constraints were found. The shortest possible uniqueness constraint is used as its primary reference.

Explanation

No primary reference scheme is defined for an object type, but the database has found a way to create a primary reference.

Solution

The database will choose the identification scheme with the fewest components as the primary reference.


C1004

<Constraint>: Uniqueness constraint on a unary predicate is redundant.

Explanation

A unary predicate is by definition unique.

Solution

Delete the uniqueness constraint.


C1005

<Fact>: All roles in the predicate are played by value object types.

Explanation

A value object type generally is used to describe an entity object type; however, there are no entity object types present in the fact type.

Solution

Create at least one entity object type that best describes the roles in the predicate.


C1006

<Object type name>: Object type plays no role.

Explanation

The object type is not linked to any role.

Solution

Connect the object type to a role.


C1007

<Object type name>: Value Object Type playing mandatory role not recommended.

Explanation

A value object type typically has only one role, which is implied to be mandatory, or it may play many roles that are implied to be disjunctively mandatory. In either case, it should not have an explicit mandatory role constraint.

Solution

Change the object type properties to remove the mandatory role requirement.


C1008

<Constraint>: Mandatory constraint on a unary predicate causes the predicate to be redundant.

Explanation

A mandatory constraint on a unary predicate (e.g., Person smokes) implies that each member of the object type's population plays that role. If this is actually the intended meaning, it is better to simply rename the object type (e.g., to Smoker).

Solution

Either remove the mandatory constraint from the unary predicate, or remove the unary predicate and rename the object type.


C1009

Value object type (<object type name>) has no data type.

Explanation

No data type is assigned to the value object type.

Solution

Assign at least one data type to the value object type.


C1014

<Object type name>: Object type plays only optional roles, including functional roles. Consider using the "independent" setting.

Explanation

The object type appears to be intended as a lookup table.

Solution

If you intend the object type to be a lookup table, use the "independent" setting to create an independent object type. If you want it to play another role, change the object type's properties.


C1015

<Constraint>: Uniqueness constraints on a nested predicate that do not span all roles are highly unusual.

Explanation

Nested predicates generally require the uniqueness constraint to span all roles.

Solution

Either change the predicate or remove the uniqueness constraint.


C1016

<Constraint>: Nesting is only allowed for predicates with a uniqueness constraint that span all roles or one-to-one binary predicates.

Explanation

To be able to nest a predicate, it must have a uniqueness factor that spans all roles, or it must be a one-to-one binary predicate.

Solution

Change the predicate properties.


C1019

<Constraint>: Irreflexive and antisymmetric constraints combined is identical to an Asymmetric constraint.

Explanation

Combining irreflexive and antisymmetric constraints to a predicate is the same as assigning an asymmetric constraint to the predicate.

Solution

Change the constraints to be antisymmetric.


C1020

<Object type name>: Subtype has no definition.

Explanation

The subtype in the <object type name> must be defined.

Solution

Change the properties of the object type.


C1028

All roles constrained as mandatory disjunction must be played by the same object type.

Explanation

For roles to be mandatory disjunctions, they must be part of the same object type.

Solution

Reassign the roles to be part of the same object type.


C1041

<Object type name/column name>: (Object type/column) has an invalid range of <value range> specified in its Value\\Range constraint. Use a value of <value> instead.

Explanation

The range you have specified has identical "from" and "to" values as <value>.

Solution

Change the value for the range or use a single value.


C1044

<Object type name/column name>: (Object type/column) has a numeric data type but its Value\\Range constraint contains alphanumeric data.

Explanation

Value\\Range constraint for this object type contains alphanumeric characters, but the data type of the object type is a numeric type.

Solution

Change the Value\\Range constraint to contain only numeric data.


C1045

<Object type name/column name>: (Object type/column) has a fixed-point numeric data type but its Value\\Range constraint contains floating point values.

Explanation

Value\\Range constraint for this object type contains floating point values, but the data type of the object type is a fixed point numeric type.

Solution

Change the Value\\Range constraint to contain only fixed-point numeric data.


C1046

<Object type name/column name>: (Object type/column) has a textual value of <value> specified in its Value\\Range constraint that is larger than what the data type allows.

Explanation

The <value> textual value specified for the Value\\Range constraint is larger than the data type of the object type allows.

Solution

Change the textual value of the constraint.


C1047

<Object type name/column name>: (Object type/column) has a textual range of <value> specified in its Value\\Range constraint that is larger than what the data type allows.

Explanation

The <value> specified for Value\\Range constraint is larger than the data type of the object type allows.

Solution

Change the Value\\Range constraint.


C1048

<Object type name/column name>: (Object type/column) has an unsigned numeric data type but its Value\\Range constraint contains signed values.

Explanation

The Value\\Range constraint for this object type contains signed numeric values, but the data type of the object type is an unsigned numeric type.

Solution

Change the Value\\Range constraint to contain only unsigned numeric data.


C1050

<Object type name>: Object type has nested role reading <fact> that contains invalid references to one or more role players.

Explanation

The nested role contains invalid references to one or more role players.

Solution

Change the nested role's references to the role players.


C1051

<Fact>: Predicate has multiple roles with the same role name <role name>.

Explanation

At least two roles have the same name.

Solution

Make role names unique within a predicate.


C1060

Fact <fact> Row <number1> is a duplicate of the row <number2>.

Explanation

Two rows contain the same number.

Solution

Delete or renumber one of the duplicate rows of the table.


C1061

Example for fact <fact> column <number> contains no duplicates, thus it looks functional. Either the fact is not elementary or the examples are invalid.

Explanation

The examples in column <number> look functional, but the fact does not appear to be reduced to elementary. It is possible that the examples are invalid.

Solution

Either reduce the fact to multiple elementary facts or supply additional example data to be significant.


C1062

Examples for <fact> contain one or more empty rows.

Explanation

Empty rows are not allowed in examples.

Solution

Delete the empty rows.


C1063

Examples for <fact> contain one or more empty columns.

Explanation

Empty columns are not allowed in examples.

Solution

Delete the empty columns.


C1064

Examples for <fact> contain one or more empty cells.

Explanation

Empty cells are not allowed in examples.

Solution

Delete the empty cells.


C1065

Fact <fact> needs more example rows to do meaningful validation.

Explanation

The fact does not contain enough example rows.

Solution

Add more example rows to the fact.


C1066

Fact <fact> example row <number1> is a duplicate of row <number2>.

Explanation

Two rows in the fact contain the same data.

Solution

Delete one of the rows.


C1067

Examples for fact <fact> imply a more precise constraint than a full-span constraint.

Explanation

The fact type has full-span constraint, but the examples suggest that more precise constraints are possible.

Solution

Either apply a constraint that is more precise than a full-span constraint, or supply more examples to make the existing constraint significant.


C1068

Examples for fact <fact> column <number> are identical. Either the fact is not elementary or the examples are invalid.

Explanation

Elementary fact types determine the combinations of data allowed at each role position. Rows of data cannot be repeated.

Solution

Fix the example data in column <number> to match your requirements for the fact.


C1069

Fact <fact> contains a uniqueness constraint over <object type name>, which contradicts the examples.

Explanation

The examples used contradict the fact.

Solution

Either fix the fact or fix the examples so the data match.


C1070

Fact <fact> examples suggest a uniqueness constraint over <object type name>.

Explanation

The examples suggest a uniqueness constraint, but the role is not marked as unique.

Solution

If the suggestion is correct, create a uniqueness constraint. If you do not intend to include a uniqueness constraint, change the fact examples.


C1071

<Object type name>: Object type is subtype with its own reference mode, but map separate option is not selected. It has been changed to map separate.

Explanation

The database has found a contradiction between the object type and the subtype.

Solution

The database has selected the map separate option for you.


C1072

<Constraint>: Constraint internal to external predicates are ignored during build.

Explanation

Any constraints specific to an external predicate are ignored.

Solution

If you do not want these constraints ignored, remove them.


C1073

<Object type name>: Nested object type plays only optional roles. Consider setting independence property.

Explanation

Nested object types playing only optional roles is unusual.

Solution

Set an independent property for the object type.


C1074

<Object type name>: Independent subtypes require "map separate" option. This option has been chosen automatically.

Explanation

"Map separate" is required for independence subtypes.

Solution

The database has chosen the "map separate" option for you.


C1075

<Object type name>: Subtype has no designated primary supertype. <Supertype name> is chosen.

Explanation

Every subtype must have a primary association with a supertype. The database has chosen <supertype>.

Solution

If you agree with the <supertype name> chosen, do nothing. If you do not agree, designate the primary supertype.


C1076

<Primary uniqueness constraint>: Alternate primary uniqueness constraint is not suited for nested object type <object type name>. Primary flag ignored.

Explanation

In <object type name, you cannot have two primary uniqueness constraints.

Solution

No action required. The alternate primary uniqueness type is ignored. However, you may change the primary uniqueness property of this constraint to rid the warning.


C2001

The document is empty.

Explanation

There is nothing in the document.

Solution

Create a source model.


C2002

<Object type name>: Object type is an undefined "External." At least one object type with this name must be an entity or value.

Explanation

The external object type does not have a matching definition.

Solution

Add an entity or a value to the object type.


C2003

<Object type name>: Object type has no primary reference scheme.

Explanation

At least one primary reference scheme is required for an object type.

Solution

Add a primary reference scheme.


C2004

<Fact>: Role has no player.

Explanation

At least one player is required for every role.

Solution

Add a player to the role.


C2006

<Fact>: Predicate has no uniqueness constraint.

Explanation

Every predicate must have at least one uniqueness constraint.

Solution

Add a uniqueness constraint to the predicate.


C2007

<Constraint>: Uniqueness constraint on a predicate must span at least "Arity – 1" roles.

Explanation

A predicate can contain up to nine roles. If you define the predicate's arity as nine, each uniqueness constraint must span at least 8 roles. If a uniqueness constraint really does apply to fewer roles, then the predicate is not elementary and should be split into smaller predicates.

Solution

Redefine the predicate's arity, reapply a correct uniqueness constraint, or split the predicate into smaller elementary predicates.


C2008

<Object type name>: Object type plays no role.

Explanation

An object type must play a role.

Solution

Define a role for the object type.


C2010

<Object type name>: Value object types cannot have an identification scheme.

Explanation

Identification schemes are not allowed for value object types because they indentify themselves.

Solution

Change the object type properties to not include an identification scheme.


C2012

<Object type name>: Subtype has multiple supertypes that do not converge to a common root supertype.

Explanation

In most cases, you do not need to take special steps to define a supertype. However, when a subtype has more than one supertype, you must define the primary supertype for that subtype.

Solution

Change how the supertypes associate with the subtype.


C2013

Subtype is involved in a circular subtype graph definition.

Explanation

A subtype graph must not be circular because only proper subtypes are allowed.

Solution

Remove the circular linkage.


C2014

Immediate subtype relationship is implied transitively.

Explanation

Another subtype path is present that makes this relationship transitive.

Solution

Remove the transitive subtype linkage.


C2015

Subtype has different type than its supertype.

Explanation

The subtype and its supertype do not have matching data types.

Solution

Match the data types.


C2017

<Object type name>: Object type is labeled as independent and plays mandatory roles.

Explanation

The independence of the object type is implied by the object type's mandatory role.

Solution

Remove the independent property.


C2018

<Constraint>: Mandatory constraint involves role sequence whose subset is involved in another mandatory constraint.

Explanation

A mandatory role cannot be involved in a mandatory disjunction.

Solution

Either remove the single-role mandatory constraint or remove the mandatory role from the mandatory disjunction.


C2019

<Object type name>: Object type plays no role other than those in the reference scheme. Consider using the "independent" setting.

Explanation

The object type does not participate in significant fact roles other than those used for its reference scheme.

Solution

Use the "independent" setting to create an independent object type.


C2020

<Constraint>: Uniqueness constraint is implied by other constraints.

Explanation

No uniqueness constraint is required.

Solution

Remove the constraint.


C2021

<Constraint>: Constraint is of unknown type. Please contact technical support.

Explanation

The constraint is an unknown type.

Solution

Remove the constraint or contact technical support for additional help.


C2022

<Constraint>: Uniqueness constraint on a unary predicate is invalid.

Explanation

Unary predicates are already unique.

Solution

Remove the uniqueness constraint.


C2023

<Constraint>: The <num> role sequence has an ambiguous join object type <object type name>.

Explanation

A constraint must have a unique join path where one role in the constraint can pass through an object type to reach each of other roles in the constraint. However, this constraint has an ambiguous join path through <object type name>.

Solution

Remove the constraint and reapply the correct constraint.


C2024

<Constraint>: The <num> role sequence involves a possible join object type <object type name>.

Explanation

The constraint involves one or more roles that are played by an object type that also appears to serve as a join path object type.

Solution

Remove the constraint and reapply a correct constraint.


C2025

<Constraint>: The <num> role sequence has no join object type.

Explanation

A constraint must have a unique join path where one role in the constraint can pass through an object type to reach each of the other roles in the constraint, but the constraint has roles that are disjoined.

Solution

Remove the constraint and reapply a correct constraint around a common join object type.


C2026

<Constraint>: Role is constrained as mandatory, but has no player.

Explanation

Mandatory roles require players.

Solution

Add a player to the role.


C2027

<Constraint>: Constraint has no roles.

Explanation

Roles are required for constraints.

Solution

Add a role to the constraint.


C2028

<Constraint>: Primary uniqueness constraint is not valid. Remove the "Primary" setting.

Explanation

The primary uniqueness constraint is not formed properly to be used as a primary uniqueness constraint.

Solution

Remove the Primary setting or change the primary uniqueness constraint.


C2029

<Constraint>: This <ring constraint type> constraint is implied by an existing exclusion constraint.

Explanation

An existing exclusion constraint makes the <ring constraint type> constraint unnecessary.

Solution

Remove the <ring constraint type> constraint.


C2030

<Constraint>: Ring constraint is undefined.

Explanation

Ring constraints must be defined (e.g., irreflexive or asymmetric).

Solution

Define the ring constraint.


C2031

<Constraint>: Symmetric constraint cannot be specified with a <constraint type> constraint.

Explanation

Symmetric constraint cannot be specified with asymmetric, antisymetric, acyclic, or exclusion constraints.

Solution

Remove symmetric or other constraints.


C2032

<Constraint>: Intransitive constraint on a binary predicate with a simple uniqueness constraint is redundant. Use an irreflexive constraint instead.

Explanation

The intransitive constraint is not applicable in this instance.

Solution

Remove the intransitive constraint, and add the irreflexive constraint.


C2033

<Constraint>: Role sequences in <constraint type name> constraint have roles played by incompatible object types.

Explanation

Mandatory disjunction must be applied to the roles played by a common object type.

Solution

Remove the constraint and reapply a correct constraint.


C2034

<Constraint>: Frequency constraint cannot be specified on the same role sequence that is constrained by a uniqueness constraint.

Explanation

Frequency constraints and uniqueness constraints cannot exist on the same role sequence.

Solution

Remove either the frequency constraint or the uniqueness constraint from this role sequence.


C2035

<Constraint>: The role should be mandatory without a subset/equality constraint, or the mandatory constraint should be removed on the opposite end.

Explanation

Either the role should be mandatory without a subset/equality constraint or the role should have the subset/equality constraint and not be mandatory.

Solution

Either remove the subset/equality constraint or remove the mandatory constraint.


C2036

<Constraint>: The subset constraint is redundant.

Explanation

The subset constraint is unnecessary here.

Solution

Remove the subset constraint.


C2037

<Constraint>: The equality constraint is redundant.

Explanation

You do not need the equality constraint.

Solution

Remove the equality constraint.


C2038

<Constraint>: The equality constraint should be a subset constraint from the <num1> sequence to the <num2> sequence.

Explanation

The subset constraint is applied such that subset roles and superset roles are reversed.

Solution

Remove the constraint and reapply a correct subset constraint.


C2039

<Constraint>: The <num> role sequence in the exclusion constraint cannot be populated.

Explanation

The designed <num> role sequence cannot be populated if the exclusion constraint is enforced. Either the facts in the sequence are not significant or the constraint is wrongly applied.

Solution

Either remove the constraint and reapply a correct exclusion constraint or remove the facts in the <num> sequence.


C2040

<Constraint>: Invalid number of role sequences in <constraint type name> constraint.

Explanation

The number of role sequences in the constraint is incorrect.

Solution

Remove the constraint and reapply a correct constraint.


C2041

<Object type name>: Value object types cannot be subtypes.

Explanation

A subtype cannot contain value object types.

Solution

Remove the subtype or turn the subtype object type into an entity.


C2042

<Constraint>: Intransitive constraint is implied by a simple uniqueness and a <constraint type name> constraint on the predicate.

Explanation

The intransitive constraint is not necessary because it is already implied.

Solution

Remove the intransitive constraint.


C2043

<Constraint>: Ring constraint can only be expressed on the two roles of the same predicate played by the same object type.

Explanation

The ring constraint cannot be applied to roles without a common object type.

Solution

Remove the ring constraint.


C2044

<Object type name>: Subtype may not have a value constraint.

Explanation

You cannot have a value constraint on a subtype.

Solution

Remove the value constraint from the subtype.


C2045

<Object type name>: Value object type cannot play a functional role.

Explanation

You cannot have a value object type play a functional role.

Solution

Remove the functional role from the value object type.


C2046

<Object type name>: Value object types cannot be labeled as independent.

Explanation

An independent label constraint is not allowed for value object types.

Solution

Remove the independent label constraint.


C2047

Value constraint is not allowed for external object type <object type name>.

Explanation

External object type <object type name> cannot have a value constraint.

Solution

Remove the value constraint.


C2048

Value constraint is not allowed for entity object type <object type name> that does not have a reference scheme.

Explanation

Value constraint can only be applied to an entity with a reference mode.

Solution

Remove the value constraint.


C2049

<Fact>: Predicate is an undefined "External." At least one predicate with this text must not be external.

Explanation

This predicate is an external, but there is no predicate that has the same verbalization.

Solution

Either change the predicate properties to be non-external or correct the predicate text.


C2050

<Constraint>: Frequency constraint without maximum must have the minimum greater than 1.

Explanation

If no maximum is defined, the value in the minimum frequency is considered to be the single frequency and must be greater than one.

Solution

Either add a minimum value greater than 1 or add a maximum value.


C2051

<Constraint>: Frequency constraint minimum and maximum values are impossible to enforce.

Explanation

The minimum value must be a smaller number than the maximum value.

Solution

Change the minimum and maximum values.


C2052

<Constraint>: Zero is an invalid frequency value.

Explanation

You must have a frequency value greater than 0.

Solution

Enter a frequency value greater than 0.


C2053

<Constraint>: Frequency cannot span all the roles in a single predicate.

Explanation

A frequency constraint can span only maximum of N-1 roles where N is the arity of the predicate.

Solution

Change the frequency constraint so it does not span all the roles of a single predicate.


C2055

<Object type name>: The chosen primary reference of this object type causes a circular reference.

Explanation

The chosen primary reference is either directly or indirectly involved in the primary reference scheme of the object type.

Solution

Remove the primary uniqueness constraint and reapply a correct primary uniqueness constraint.


C2061

<Object type name>: Nested object type cannot be external.

Explanation

External nested object types are not allowed.

Solution

Change the object type properties.


C2062

<Fact>: Predicate nested as <object type name> cannot be external.

Explanation

An external predicate cannot be used for nesting.

Solution

Change the predicate properties.


C2063

<Constraint>: Constraint involves two or more distinct facts that are identical in reading.

Explanation

The predicates involved in the constraint have the same reading so appear as the same facts.

Solution

Remove the constraint.


C2064

<Constraint>: Constraint involves facts that have missing role players.

Explanation

Constraint facts must have role players.

Solution

Assign role players to the facts.


C2065

Subtype link involves object type that is used in the reference scheme of other object types in the same subtype graph.

Explanation

The subtype link connects with an object type that is used in the reference scheme of other object types in the same subtype graph.

Solution

Remove the subtype link.


C2066

<Fact>: Unary predicate is nested to form an object type.

Explanation

Unary predicates cannot be nested.

Solution

Remove the nesting.


C2067

<Constraint>: Acyclic constraint cannot involve mandatory roles.

Explanation

Roles involved in an acyclic constraint cannot be mandatory.

Solution

Remove the mandatory constraint or change the type of ring constraint.


C2068

<Constraint>: Acyclic constraint conflicts with a subset or equality constraint between the same roles.

Explanation

One of the roles in the acyclic constraint cannot be involved with a subset or equality constraint with the other role in the constraint.

Solution

Either remove the constraint or remove the subset or equality constraint.


C2069

Constraint>: <Constraint type name> constraint contains role sequences with conflicting uniqueness constraint pattern.

Explanation

The role sequences in the <Constraint type name> constraint have conflicting uniqueness constraint patterns.

Solution

Either remove the constraint or remove the uniqueness constraints.


C2101

<Object type name>: Nested object types are not allowed to be alias types.

Explanation

Nested object type cannot be mapped to a distinct type or domain type.

Solution

Change the properties of the nested object type to no type mapping or to a named row type.


C2102

<Object type name>: Value object types are not allowed to be row types.

Explanation

Value object type cannot be mapped to a composite type.

Solution

Change the type mapping properties of the value object type.


C2103

<Constraint>: Alias object type < object type name> can identify only one element using a primary uniqueness constraint.

Explanation

<Object type name> object type can be mapped to a distinct or domain type only if its reference scheme involves just one other object type.

Solution

Either change the composite type property of the object type or choose a different primary reference scheme for the object type.


C2104

<Object type name>: Aliased object type by <other object type name> must be a built-in type, an unnamed row, or a collection type.

Explanation

This object type can only be aliased as a domain type if it is a built-in type, an unnamed row, or a collection type.

Solution

Change the properties of this object type.


C2105

<Object type name>: Aliased object type by <other object type name> must be a built-in type, a named row, or another distinct type.

Explanation

This object type can only be aliased as a distinct type if it is a built-in type, a named row, or another distinct type.

Solution

Change the properties of this object type.


C2106

<Object type name>: Value domain can only alias a built-in type.

Explanation

The value object type must be a value object type to be a domain.

Solution

Change the data type properties of the object type.


C2107

<Object type name>: Unnamed row type must exist as an attribute of another object.

Explanation

An unnamed row type must be an attribute of another object type.

Solution

Change the composite type properties.


C2108

<Object type name>: Alias type may reference only one object.

Explanation

The object type cannot have attributes and be an alias type at the same time.

Solution

Change the composite type properties.


C2109

<Fact>: Collection type may not be specified on a unary predicate.

Explanation

Unary predicates cannot specify collection type.

Solution

Remove the collection type constraint.


C2110

<Fact>: Collection type on a binary predicate requires a uniqueness constraint spanning both roles.

Explanation

For a binary predicate to have a collections type, the predicate must specify a uniqueness constraint that spans both roles.

Solution

Either remove the collection type from the binary predicate or add a uniqueness constraint that spans both roles.


C2111

<Fact>: Collection type may not be specified on both roles of a binary predicate.

Explanation

Collection type can be specified on one role of a binary predicate, but not both.

Solution

Remove one of the specified collection types from the predicate.


C2112

<Object type name>: Typed object type <other object type name> cannot be used for the primary reference scheme of untyped object type, which is also attribute of other object types.

Explanation

Typed object type can only be involved in the primary reference scheme of another typed object type.

Solution

Either change the composite type property of this object type or the change the other object type.


C2113

<Object type name>: Untyped entity object type may not be used to define an attribute for typed object type <other object type name>.

Explanation

Using an untyped entity object type as an attribute for a typed object type <other object type name> is not allowed.

Solution

Change the composite type property of this object type.


C2114

<Object type name>: Untyped entity object type may not play a role in the nested predicate by a typed object type <nested object type name>.

Explanation

If a nested object type has composite type property, then the entity object types in playing the roles of the nested predicate must also be typed.

Solution

Change the composite type property of this object type.


C2115

<Object type name>: Subtype relationship from <supertype object type name> must exist between non-types or named row types.

Explanation

The subtype's composite type property must be identical to that of supertype's composite type property.

Solution

Either change the composite type property of this object type or change the supertype's composite type property.


C2116

<Object type name>: Object types mapping to a home table cannot be unnamed row type or domain type.

Explanation

If an object type maps to a home table, the object type cannot be an unnamed row type or a domain type.

Solution

Change the composite type property of this object type.


C2117

<Type name>: Type has more than one definition.

Explanation

The type name supplied for the composite type property is not unique.

Solution

Change the type name property of this object type.


C2118

<Table Name>: Table/Entity has no primary identifier defined.

Explanation

Primary identifier is required for the Table/Entity.

Solution

Define the primary identifier.


C2119

<Fact>: Predicate cannot involve view and non-view object types.

Explanation

Either a view or a non-view object type can be used with a predicate, but not both.

Solution

Change the object type properties.


L1011

<Table name>: External table cannot exist in the model that contains the actual table.

Explanation

The actual table and the external table must exist in different models.

Solution

Change the external table properties.


L1012

<Table or column name>: <name> is an undefined "External."

Explanation

<Name> must be defined as "External."

Solution

Define <name> as External.


L1014

<Category name>: Category symbol does not have an owner.

Explanation

An owner is required for a category symbol.

Solution

Define the owner for the category symbol.


L1015

<Table1 name>: Table <table2 name> is involved in a circular category/inheritance relationship.

Explanation

<Table1 name> is either directly or indirectly a parent of <table2 name> or vice-versa. Circular relationships are allowed only if one of the relationships involved is non-identifying.

Solution

Either make one of the relationships non-identifying or remove one of the relationships from the model.


L1016

<View column name>: View column data type is not consistent with projected column <view column name>.

Explanation

The projected column does not work with the view column data type.

Solution

Edit the properties of the column so that the column data type and the projected column match.


L1017

<Relationship name>: Relationship must be between two tables.

Explanation

There must be two tables to have a relationship.

Solution

Edit the relationship to be between two tables.


L2010

<Column name>: Column cannot have value/range or check clause when defined as a collection type.

Explanation

If the column is defined as a collection type, value/range and check clause are not allowed.

Solution

Either change the column definition or remove the value/range or check clause.


L2100

<Relationship name>: Relationship is not fully connected.

Explanation

The relationship is not properly defined in the table.

Solution

Connect the relationship.


L4009

<Index name>: Alternate key on table <table name> and alternate key <Index name> (on same table) are functionally identical and specified as unique constraints.

Explanation

Two alternate keys are identical, but are specified as unique.

Solution

Either change one of the alternate keys or remove the unique constraints.


L4011

<View name>: View column is projected on a column <view column name> whose owner occurs after the view definition.

Explanation

A definition owner must occur in a column before the view definition.

Solution

Edit the column definition in the view.


L4012

<View name>: View has join expression involving a column <view column name> whose owner occurs after the view definition.

Explanation

A definition's owner must occur in a column before the view join expression.

Solution

Edit the column definition in the view.


L4013

<View name>: View has join expression involving the same column.

Explanation

A column cannot be joined to itself.

Solution

Edit the join expression of the view.


L7002

<Field name>: Field cannot have value/range or check clause when defined as a collection type.

Explanation

Value/range and check clause are not allowed when a field is defined as a collection type.

Solution

Edit the field definition.


L8001

<Database code type>: <Code name> has no body defined.

Explanation

There is no code.

Solution

Define the code.


L8002

<Database code type>: <Code name> has no name defined.

Explanation

There is no name.

Solution

Define the name.


L8003

<Database code type>: <code name> name was not found in the code body. Is this correct?

Explanation

The code is not defined.

Solution

Check the code body to be sure that names you have chosen are correct.


L8004

<Database code type>: <code name> name is not unique.

Explanation

Names must be unique.

Solution

Rename either the table or the column name.


M1002

<Object type>: Object type's data type does not match other object types with same name/name space.

Explanation

As models have been merged, two object types with the same name/name space but different data types were found.

Solution

Either change the name/name space of one of the object types or change the data types of the two object types to match.


M1003

<Object type>: Object type description does not match other object types with same name/name space.

Explanation

As models have been merged, two object types with the same name/name space but different definitions were found.

Solution

Either change the name/name space of one of the object types or change the definition of the two object types to match.


M1004

<Object type>: Object type subtype definition does not match other object types with same name/name space.

Explanation

As models have been merged, two object types with the same name/name space but different subtype definitions were found.

Solution

Either change the name/name space of one of the object types or change the two subtype definitions to match.


M1005

<Object type>: Object type independent setting does not match other object types with same name/name space.

Explanation

As models have been merged, two object types with the same name/name space but different independent settings were found.

Solution

Either change the name/name space of one of the object types or change the independent settings to match.


M1006

<Object type>: Object type's reference scheme has more than one definition.

Explanation

Only one definition is allowed for an object type's reference scheme.

Solution

Remove all but one definition.


M1007

<Predicate name>: Predicate [fact] has more than one definition.

Explanation

Predicates can have only one definition.

Solution

Remove all but one definition.


M1009

<Object type>: Object type value constraint does not match other object types with same name/name space.

Explanation

As models have been merged, two object types with the same name/name space but different value constraints were found.

Solution

Either change the name/name space of one of the object types or change the value constraint to match.


M1012

<Document name>: Source model cannot be opened because it is marked as read-only.

Explanation

Read-only source models cannot be opened.

Solution

Either open the document and save it to another file name or turn off the read-only selection in the document's Properties box.


M1013

<Document name>: Document is marked as read-only.

Explanation

Read-only documents cannot be saved.

Solution

Either open the document and save it to another file name or turn off the read-only selection in the document's Properties box.


M1014

<Document name>: Unable to save changes to document.

Explanation

If you opened a read-only document, edited it, and tried to save it, it is unable to save.

Solution

Either open the document and save it to another file name or turn off the read-only selection in the document's Properties box.


M1015

<Database model name>: Project contains both object relational and non-object relational models. The database model project will be built as non-object relational.

Explanation

In one or more of the source models for the project, object relational shapes are used, and one of the merge documents contains only relational shapes.

Solution

If you do not want to receive this warning again, remove the object relational models.


M1016

<Document name>: <Object type> has multiple primary identifiers.

Explanation

Object type has multiple identifiers marked as primary.

Solution

Choose a single identifier as the primary.


M2001

<Object type>: Object Type has more than one definition. Make one of the object types "External."

Explanation

If an object type has more than one definition, make one of the object types "External."

Solution

Make one of the object types "External."


M2003

<Object type>: Subtype has more than one definition. Please choose one definition and remove the others.

Explanation

More than one definition for a subtype is not allowed.

Solution

Choose one of the definitions to keep and remove the others.


M2005

<Constraint name>: Constraint has more than one definition. Please remove all but one.

Explanation

No more than one definition for a constraint is allowed.

Solution

Choose one of the definitions to keep and remove the others.


M2501

<Table name>: Table has more than one definition. Please change the conceptual name of the offending table or remove the duplicate.

Explanation

No more than one definition for a table is allowed.

Solution

Either change the conceptual name of the table or remove the duplicates.


M2502

<Column name>: Column has more than one definition. Please change the conceptual name of the offending column or remove the duplicate.

Explanation

More than one definition for a column is not allowed.

Solution

Either change the conceptual name of the column or remove the duplicates.


M2503

<Foreign key name>: Foreign key has more than one definition.

Explanation

No more than one definition for a foreign key is allowed.

Solution

Either change the conceptual name of the foreign key or remove the duplicates.


M2504

<Category name>: Category has more than one definition.

Explanation

No more than one definition for a category is allowed.

Solution

Either change the conceptual name of the category or remove the duplicates.


P1018

<Index name>: Index name in the table <table name> is a reserved word in the target database management system.

Explanation

The index name conflicts with a reserved word in the database management system. Examples of reserved words include Select, Table, and Create. Check the documentation of the target database management system for a list of reserved words.

Solution

Rename the index.


P1034

<Table name.column name> Only one column per table is allowed to have the AUTOINC data type. Please change the column data type accordingly.

Explanation

AUTOINC portable data type is allowed in only one column per table.

Solution

Select which column will keep the AUTOINC data type, and change the others in the table.


P1038

<Table name>: No check name is specified for this table.

Explanation

Check names for a table must be defined.

Solution

Specify a check name for the table.


P1039

<Table name.column name>: No check name is specified for the column.

Explanation

Check names for a column must be specified.

Solution

Specify a check name for the column.


P1040

<Field name>: No check name is specified for the field.

Explanation

Check names for a field must be specified.

Solution

Specify a check name for the field.


P1041

<Check name>: Check name exceeds the maximum characters (<number>) allowed by the target database.

Explanation

The check name exceeds the number of characters allowed by the target database management system.

Solution

Shorten the type or physical name.


P1042

<Check name>: Check name contains invalid characters.

Explanation

The check name contains characters not allowed in the target database management system. Examples of invalid characters include #, $, and @. Check the documentation of the target database management system for a list of invalid characters.

Solution

Remove the invalid character in the check name and replace it with a valid character.


P1043

<Check name>: No check definition is defined.

Explanation

Check definitions must be defined.

Solution

Define the check definition.


P1044

<Table name>: Table has more than one check clause defined. The target database management system only supports the creation of one table level check clause for each table.

Explanation

Only one check clause is allowed per table.

Solution

Remove extra check clause definitions from the table.


P1045

<Table name.constraint name> Target database management system does not allow the creation of multiple unique constraints on the same set of columns. You need to remove this unique constraint.

Explanation

Multiple unique constraints are not allowed on the same set of columns in the target database management system.

Solution

Remove the excess unique constraints.


P1046

<Table name.constraint name> Target database management system does not allow the creation of multiple index constraints on the same set of columns. You need to remove this unique index.

Explanation

Multiple index constraints are not allowed on the same set of columns in the target database management system.

Solution

Remove the excess index constraints.


P1047

<Table name.constraint name> Target database management system only allows the creation of one unique index or unique constraints on the same set of columns. You cannot have both at the same time.

Explanation

You can either have a unique index or unique constraints in the target database management system, but not both.

Solution

Remove either the unique index or the unique constraints.


P1048

<Table name.constraint name> Unique index with the option to create unique constraint on top of it is not allowed in the target database management system. You need to change the index type.

Explanation

An option for a unique constraint created on top of a unique index is not allowed in the target database management system.

Solution

Change the index type to be either "Unique Index only" or "Unique Constraint only."


P1049

<Table name.constraint name> Target of the foreign key relationship can only be a primary key. You need to change the target column(s) before you can continue.

Explanation

Primary keys are required and must contain unique identifying values. Foreign key relationships must be targeted to a primary key.

Solution

Change the target columns to primary.


P1051

<Table name.trigger name> No trigger definition is specified.

Explanation

Triggers in the model must be defined.

Solution

Define the trigger.


P1053

<Type name>: Type name exceeds the maximum characters (<length>) allowed by the target database management system.

Explanation

Table names in the target database management system must be shorter.

Solution

Create a new table name that is shorter.


P1707

<Column name>: Check clause is not supported by the target database management system using SQL construct. The check clause is created as comment block.

Explanation

Target database management system does not support the creation of check clause on table/column.

Solution

Check clause is converted to a comment in the DDL file.


P1708

<View name>: View is not supported by the target database management system using SQL construct. The view is created as comment block.

Explanation

Target database management system does not support the creation of view.

Solution

View is created as a comment in the DDL file.


P1709

<Table name.constraint name> Target database management system does not support the creation of unique constraint. Only a unique index is created instead.

Explanation

Unique constraint is not supported in the target system.

Solution

A unique index is created instead.


P1710

<Table name.column name> primary key/unique constraint column is declared as nullable. Table can be created, but you cannot insert null value into this column because it violates the constraint imposed by the database management system.

Explanation

Primary keys are required and must contain unique identifying values. Null values generally indicate optional columns. Although allowing a null value in a primary key is legal, it is not allowed for this column.

Solution

Edit the column to change the definition to be not nullable.


P2101

<Trigger name>: Double-quote character is used in the code definition. This could be a problem when using ODBC to create the schema. It is recommended that you change it to single quote character.

Explanation

Double-quote characters in the code definition can be misinterpreted by ODBC.

Solution

Change all quotes to single quote characters in the trigger body code definition.


P2450

<Table name.column name> Nested table type column cannot have not nullable constraint specified. This constraint is ignored during DDL generation.

Explanation

Nested table type columns must be nullable.

Solution

If you don't want the column ignored, change the nested table type column constraint to nullable.


P2451

<Aggregate name>: Domain is created based on set collection type. It is created as nested table collection type during DDL generation.

Explanation

This domain is created as a nested table collection type.

Solution

If you do not want the collection type to be a nested table, change the collection type.


P2452

<Distinct type name>: Distinct type and unnamed row types are not supported in Oracle 8 database management system. No DDL is generated for this distinct type.

Explanation

Oracle 8 does not support distinct type or unnamed row types.

Solution

If you want the DDL SQL to be generated, remove the distinct type and unnamed row types.


P2453

<Domain type name>: Domain based on data type without any collection is not supported. No DDL is generated for this aggregate.

Explanation

No DDL will be generated if the domain is based on data type without any collection.

Solution

If you want DDL to be generated, change the data type to include collection.


P2454

<Aggregate name>: Type inheritance is not supported for Oracle 8 database management system. No super type is associated with this aggregate.

Explanation

Oracle 8 does not support type inheritance.

Solution

If you want a super type to be associated with the type inheritance, don't use it when you work in Oracle 8 database management system.


P3701

<Table name>: Microsoft Access does not support the creation of check clauses using SQL create table construct. The check is only created using DAO if you have a connection to the MDB file during DDL generation.

Explanation

The SQL construct support in the Microsoft Access ODBC does not allow check clause to be part of the SQL statement. To populate the check clause, the program creates the check using DAO during the post-DDL generation phase when there is a live connection to the Microsoft Access MDB file. Live connection to the database is required.

Solution

If the process above is unacceptable, add the check clause within the Microsoft Access database management system.


P4451

<Table name.index name> Unique index cannot be created on Corel Paradox 5.x and below type table. Only comments are generated in the DDL file.

Explanation

Corel Paradox version 5.x and below do not support unique indexes on table. Only comments will be generated in the DDL file.

Solution

Either review the comments after DDL generation or consider creating non-unique index instead.


P4452

<Table name.index name> For single-column index, IDAPI engine requires the index name to be the same as the column name. Column <column name> is the key name if you use IDAPI to create the index.

Explanation

IDAPI requires the index name to be the same as the column name for single column indexes.

Solution

If the solution above is unacceptable, you can change the index name to be the same as the column name to avoid the warning.


P4453

<Identifier name>: Identifier contains character(s) that are not allowed by some ODBC drivers. The DDL generated might not be able to run correctly using ODBC driver.

Explanation

At least one character contained in the identifier name is not being read properly by ODBC.

Solution

Remove the character if you want the table to run correctly using ODBC drivers.


P4454

<Table name.foreign key name> Corel Paradox supports the update/delete rule to be RESTRICT or CASCADE only. The foreign key is created with RESTRICT flag instead.

Explanation

The update/delete rule in Corel Paradox must be either RESTRICT or CASCADE.

Solution

Either change your referential integrity flag to be RESTRICT/CASCADE or create functions to enforce the rule within the Corel Paradox database management system.


P4455

<Table name.check name> Table-level check clauses are not supported in Corel Paradox. No check is created using IDAPI engine.

Explanation

Corel Paradox does not support check clause at the table level.

Solution

Remove the table level check clause and create a function to enforce the check.


P4456

<Table name.check name> Only column-level check for datatype ALPHA, (LONG) INTEGER, and SHORT can be created using IDAPI engine. Check <check name> will not be created.

Explanation

Column level checks can only be created from the Microsoft Visio program for data type ALPHA, (LONG) INTEGER and SHORT via IDAPI engine.

Solution

Remove the column-level check clause or create these checks within the Corel Paradox database management system.


P4950

<Keyword that is a SQL reserved word> is a reserved word and may result in syntactic ambiguities in SQL statements.

Explanation

SQL reserves certain words. If you use a keyword that is a SQL reserved word, you risk syntactic ambiguities in SQL statements.

Solution

Use a different word.


X1000

Relationship between <Entity1> and <Entity2> is not supported by ERwin. ERwin requires all relationships to be from parent primary key to child primary key/non-primary key.

Explanation

To convert to ERwin, all relationships must be from parent primary key to child primary key /non- primary key.

Solution

Change the relationships.


X1001

Foreign key column <child table name.column name> has different conceptual name than parent column <parent table name.column name>. ERwin migrates the parent's conceptual name to the foreign key.

Explanation

ERwin requires that the FK column and the parent column have the same conceptual name.

Solution

If you want the parent column and the FK column not to be combined, change the name of either the parent column or the FK column.


X1002

Column <table name.column name> has the same conceptual name <conceptual name> as one or more other columns in the same table. ERwin will not import this column.

Explanation

Duplicate column names are not allowed in ERwin. Columns with duplicate names will not be imported.

Solution

If you want the columns imported, create unique names for each column.


C2070

<Constraint>: The equality constraint involving two facts results in the same population. One of the facts should be removed.

Explanation

The equality constraint between two facts cause the population of both facts to be identical; that is, the two facts are identical except for the reading.

Solution

Either remove the constraint or remove one of the facts.


C2054

<Constraint>: This <ring constraint type> constraint is implied by an existing <constraint type> constraint.

Explanation

The <ring constraint type> constraint is not needed.

Solution

Remove the constraint.


C2056

<Fact>: Predicate text cannot be empty.

Explanation

Predicate text must be defined.

Solution

Define text for the predicate.


C2060

<Object type name>: Object type has no name.

Explanation

Object types must be named.

Solution

Name the object type.


C2100

<Object type name>: Subtype object type that also is a composite type must be a named row type.

Explanation

Subtypes cannot be mapped to unnamed row type, distinct type, or domain type.

Solution

Change the property of the object type so that it either maps to no composite type or to a named row type.


P1705

Cannot create foreign key constraint <constraint name>: on table <child table name> in the target database management system. Parent columns need to be in the primary key list in the parent table <parent table name>.

Explanation

To create a foreign key constraint, the parent columns must be in the primary key list of the parent table. Primary keys are required and must contain unique identifying values.

Solution

Edit the parent columns and then create the foreign key constraint.


P1706

<Identifier name>: Identifier name contains special character(s). It will be replaced with character ‘_' during DDL generation for the target database management system.

Explanation

There are disallowed special characters in the identifier name. The characters will be replaced with ‘_'.

Solution

The program performs the tasks automatically for you.


P4902

<Table name>: Table must be typed in order to participate in an inheritance relationship.

Explanation

To be included in an inheritance relationship, a table must be assigned a type first.

Solution

Assign the table to a type.


About DBMS connections (Database model)

You must provide information about the database management system (DBMS) to which you are connecting when you use Microsoft Visio to:

  • Reverse engineer an existing database.
  • Refresh a model after you reverse engineer a database.

You must specify the following:

  • A Microsoft Visio database driver.
  • An associated vendor-supplied 32-bit ODBC (Open Database Connectivity) driver or a generic ODBC driver or OLE DB provider.
  • A data source.


About the Microsoft Visio database modeling templates

The database solution includes three templates that you can use to design and document logical database models for relational and object-relational databases.

Click to show or hide information. The Database Model Diagram template

Use this template to diagram and document logical database models using relational notation or IDEF1X notation. In addition to diagramming models from scratch, you can create new models by reverse engineering existing physical databases.

Click to show or hide information. The Express-G and ORM Diagram templates

The Express-G and ORM Diagram templates are also included in the database solution, so you can diagram your database model in a specific style of notation. You cannot use these templates to reverse engineer an existing database.


About models that represent relational and object-relational databases

The Database Model Diagram template and the ER Source Model template open with the

  • Entity Relationship stencil for creating relational database model diagrams.
  • Object Relational stencil for creating object-relational database model diagrams with extended functionality, such as composite data types, type inheritance, and table inheritance.

You use the shapes on the Entity Relationship or Object Relational stencil to represent tables, columns, and other components as they appear in a database.

Relational and object-relational notations share many common elements, such as

  • Tables or entities, which you can create by using the Entity shape.
  • Columns or attributes, which you create by double-clicking an Entity shape and then clicking Columns in the Categories list in the Database Properties window. The relational and object-relational notations support primary keys, foreign keys, alternate keys, and indexes.
  • Relationships, which you can create by using the Relationship shape, which includes support for referential integrity and IDEF1X cardinality constraints.


About database modeling shapes

The database modeling stencils include SmartShapes symbols that are programmed to behave in very specific ways in accordance with the modeling method you choose.

Click to show or hide information. Entity Relationship stencil

Shapes in the Entity Relationship stencil can be used to model relational databases:

  • Entity shapes represent the tables in which you store data.
  • Relationship connector shapes show the parent-child relationships between tables. A child table contains information derived from a parent table.
  • View shapes contain columns derived from other tables and represent alternative combinations of information stored in the database.
  • Parent To Category connector shapes link a parent table to a Category shape.
  • Category shapes show the relationship of multiple child tables to a parent table.
  • Category To Child connector shapes link a child table to a Category shape and establish foreign key relationships with the parent table.
  • Dynamic Connector shapes connect shapes but do not create relationships between shapes.

Click to show or hide information. Object Relational stencil

Shapes in the Object Relational stencil can be used to model either relational or object-relational databases. Object-relational tables can include complex types, which act like nested records.

This stencil includes all of the shapes on the Entity Relationship stencil, plus the following shapes:

  • Table Inheritance connector shapes set child tables to inherit or derive the attributes of a parent table automatically.
  • Type Inheritance connector shapes set a type to inherit or derive the attributes of a parent type automatically.
  • Type shapes enable you to embed subtables in an object-relational database model by defining a type that has multiple columns and assigning it as the data type for a column in a table.

Click to show or hide information. ORM Source Model stencil

Shapes in the ORM Source Model stencil can be used to create conceptual database models:

  • Object Type shapes represent different kinds of entities (such as Person or Car) and values (such as CountryCode or RoomNumber).
  • Predicate shapes represent roles and associations in which objects participate.
  • Subtype Relationship connector shapes link subtypes to their supertypes.


About database document options

When you use shapes from the Entity Relationship or the Object Relational stencil, you can choose the notation type for the shapes and customize other display options.

Click to show or hide information. How shapes in a database model diagram appear on the screen

The way the shapes in a database model diagram appear on the screen is determined by the notation type, IDEF1X or Relational, and other options you select in the Database Document Options dialog box.

Note As you work, you can switch easily between IDEF1X and Relational notation—this is helpful if you are collaborating with a colleague who prefers a different notation.

Click to show or hide information. IDEF1X notation

Describes a data model as entities that have attributes and participate in relationships. Each entity is depicted as a rectangle that shows the entity's name directly above and its attributes inside. A relationship between entities is portrayed by a one-dimensional (1-D) connector shape.

Show me an example

Click to show or hide information. Relational notation

Uses tables, columns, and keys to describe a data model. Each table is represented by a rectangle that shows the table's name at the top. Columns of a table are listed vertically underneath the name of the table. A foreign key relationship is shown by a 1-D connector shape.

Show me an example

Click to show or hide information. Other display options

You can further customize your database model diagram by specifying

  • How relationships are drawn.

For example, you can display referential integrity and show relationships by using Crow's Foot notation, a connector style that indicates relationships between entity shapes.

  • The level of detail displayed in a database model diagram.

For example, you can show and hide levels of detail to display all the model's elements or only the ones you want, such as the primary keys and foreign keys.

Click to show or hide information. Annotations for Entity shapes

You can show or hide an Entity shape's column annotations. Column annotations include Primary key (PK), Foreign Key (FKn), Alternate Keys (AKn), Unique (Un), and Index (In).

You can display column annotations in either Relational or IDEF1X notation:

  • In Relational notation, column annotations are displayed in the left column.
  • In IDEF1X notation, column annotations are surrounded by parentheses and appended to the column name.


About database modeling preferences

In the Database Modeling Preferences dialog box, you can set default behavior that applies to all database modeling diagrams.

Click to show or hide information. Default preferences for logical database diagrams

You can set default behavior for

  • Removing an object from the diagram.
  • Showing relationships after adding a table to the diagram.
  • Showing relationships after adding a type to the diagram.
  • Synchronizing the conceptual and physical name of a table or column.

Click to show or hide information. Default preferences for foreign keys, prefixes, and suffixes in a logical database model diagram

You can specify the following:

  • The behavior when foreign keys are added or deleted, or when adding a foreign key causes a name conflict.
  • The prefix for the default name for objects added to a diagram.
  • The suffix for the default name for objects added to a diagram.
  • How to construct the default foreign key name that appears in a diagram.

Click to show or hide information. Default preferences for the Fact Editor

You can set input options and select the criteria that determine the completeness of a fact type in the Fact Editor.

When you set default preferences for the Fact Editor, you choose the category for which you would like to set options, and then you choose the options for that category.

Select this category | To
Input Preferences | Set guided and freeform input styles and options.
Miscellaneous Preferences | Set criteria for fact completeness and set options for preferences regarding the general behavior of the Fact Editor.
Syntax Colors | Set an option to color-encode facts and set the colors of the fact element.

Click to show or hide information. Default preferences for ORM diagrams

You can set default characteristics and behavior for

  • Removing an element from the diagram.
  • Naming conventions for object types.
  • Appending a number to the end of an object type to keep a running sequence as new objects are created.


About anchored windows and refining database models

You can refine a database model diagram by using the anchored windows associated with the Database Model Diagram, ER Source Model, and ORM Source Model templates.

To open anchored windows, on the Database menu, point to View, and then click the window you want to open.

Click to show or hide information. Anchored windows available in each template

The following table shows the windows that are available in each template.

Window | Database Model Diagram | ER Source Model | ORM Source Model
Code | X | X |
Tables and Views | X | X |
Types | X | X |
Project | X |  |
Output | X | X | X
Verbalizer | X | X | X
Object Types |  |  | X
Business Rules |  |  | X

Click to show or hide information. Code window

Lists all code for a particular database platform that is associated with your model, including code extracted from a database during reverse engineering. The Code window also provides access to the Code Editor, where you can revise existing code or write new code.

Note The Code window is not available in the ORM Source Model template.

Click to show or hide information. Tables and Views window

Appears automatically when you use the Reverse Engineer Wizard, and lists the names of tables and views extracted from the database. You can drag elements from the Tables and Views window into your diagram, where you can view them along with any associated relationships.

You can view the tables related to a particular table without viewing the entire model diagram.

Note The Tables and Views window is not available in the ORM Source Model template.

Click to show or hide information. Types window

Lists the composite types you defined, the built-in logical data types for your target database, and the portable data types you created using the User-Defined Types dialog box. Once you are familiar with the logical data type requirements of your database management system, you can assign data types to specific columns in the model.

Note The Types window is not available in the ORM Source Model template.

Click to show or hide information. Output window

Displays feedback for certain actions, such as running the Reverse Engineer Wizard or validating the model. The Output window lists progress notes, errors, and warnings in tabbed panels. Use this information to quickly navigate to warning conditions or errors in the model.

Click to show or hide information. Verbalizer window

Provides information in text form that describes what the selected shape represents. For example, if you select an Entity shape in your diagram, the Verbalizer window displays information from the Database Properties window, such as the table name, column names, and information that you typed in the Notes tab.

Click to show or hide information. Project window

Displays a list of the files that are in a project. Because you can add any type of file to a project, the primary purpose of the Project window is to specify the source model documents. This window identifies source model documents by their document type (ER source model,ORM source model, or other type) and lists each document under the appropriate subfolder.

Note The Project window is available only when using the Database Model Diagram template.

Click to show or hide information. Object Types window

Lists the object types that you have defined in an ORM conceptual model. You can drag elements from the Object Types window into your diagram, to include them in your model.

Note The Object Types window is available only in the ORM Source Model template.

Click to show or hide information. Business Rules window

Includes the following:

  • The All Folders pane displays the ORM source model drawing file (.vsd) and folders to contain objects in the model.
  • The Fact Types tab displays the fact types in your ORM model. On this tab, you can open the Fact Editor dialog box to edit an existing fact or add a new fact to your ORM model.
  • The Object Types tab displays the object types in your ORM diagram. On this tab, you can add or delete object types, locate object types in your diagram, and display the portable or physical data type for an object type.


About the Database Properties window (Database model)

The Database Properties window is an anchored window that displays the categories you use to assign properties to elements in your database model diagram.

The categories that the Database Properties window displays depend on the element that you select in your database model diagram.

For example, if you select a table in the diagram, the Database Properties window displays the categories that you use to edit the properties of the table. If you select a relationship in the diagram, the Database Properties window displays the categories that you use to edit the properties of the relationship.

For details about a category, click the category and press F1.

The Database Properties window can be closed, open, or open but hidden. The state of the Database Properties window determines how you start editing properties for any element in your database model diagram.

Click to show or hide information. If the Database Properties window is closed

  1. Open the Database Properties window by double-clicking the element whose properties you want to edit.
  2. In the Database Properties window, click the category that contains the information you want to edit, and then make your changes.

The database model diagram is updated automatically.

Click to show or hide information. If the Database Properties window is open

  1. Click the database model element whose properties you want to edit.

The Database Properties window shows the categories that are associated with the selected element.

  2. In the Database Properties window, click the category that contains the information you want to edit, and then make your changes.

The database model diagram is updated automatically.

Click to show or hide information. If the Database Properties window is open but hidden

  1. Click the database model element whose properties you want to edit.
  2. Move the pointer over the Database Properties title bar to make the window appear.
  3. In the Database Properties window, click the category that contains the information you want to edit, and then make your changes.

The database model diagram is updated automatically.

Note If you want the Database Properties window to stay open while you edit a series of database components, click the Pushpin button [image].


About categories (Database model)

Category tables are subtypes of a parent table. For example, an employee record (the parent) might contain a job-type column; category tables might include engineer, technician, and administrator. The job-type column in the parent table is the category discriminator.

Click to show or hide information. Complete versus incomplete category tables

If a category contains

  • All possible subtypes, it is considered complete. Double lines in a Category shape indicate a complete category.
  • Only some possible subtypes, it is considered incomplete. A single line in a Category shape indicates an incomplete category.

Click to show or hide information. Category discriminator

The category discriminator is an attribute in a parent table that determines the category to which an instance of a child table belongs. You define the category's discriminator attribute on the parent table.

Click to show or hide information. Categories illustrated

Complete category example

Complete category example

The Product table uses the ProdCategory column as the discriminator attribute; the double line on the category connector indicates a complete category. The three child tables are the only possible product descriptions for that category.

Incomplete category example

Incomplete category example

The Category shape connected to this Product table indicates an incomplete category. The child tables are only two of the possible product descriptions for that category.


About referential integrity and cardinality (Database model)

The principle of referential integrity requires that the foreign key values of a child table are either null or match exactly those of the primary key in the parent table.

For example, a referential integrity constraint might require that all employees be assigned to one of the company departments that are stored in another table. That way, no employee could be assigned to a department that did not exist.

The cardinality of a relationship describes how many records in a parent table can be directly related to records in a child table. Cardinality is expressed in terms such as one-to-one, one-to-many, many-to-one, or many-to-many.

Cardinality settings can be displayed only in diagrams that use IDEF1X notation.

Note Relational notation supports cardinality, but does not display it graphically.

The following table shows Cardinality settings in IDEF1X notation.

One to zero or more | One to zero or more
One to one or more | One to one or more
One to zero or one | One to zero or one
One to exactly N | One to exactly N
Zeror or one to zero or more | Zeror or one to zero or more
Zero or one to one or more | Zero or one to one or more
Zero or one to zero or one | Zero or one to zero or one
Zero or one to exactly N | Zero or one to exactly N

Click to show or hide information. How referential integrity constraints are generated

Microsoft Visio generates referential integrity constraints between related tables by using the settings you define.

Click to show or hide information. The difference between maintaining and enforcing referential integrity

To maintain referential integrity, you specify rules that govern how a change to the data values in one table in the relationship affects the data values in the other table.

When you enforce referential integrity, the foreign key (FK) relationships between related tables remain valid when you insert, update, or delete records.

Click to show or hide information. The options for enforcing referential integrity

You can select any of the following actions to enforce referential integrity. The option you select determines the effect on the child table when information in the parent table is updated or deleted.

  • No Action Makes no automatic changes to the foreign key.
  • Cascade Makes the same changes to the foreign key that are made to the primary key.
  • Set NULL Under On Parent Update, Set NULL sets the entire foreign key to null. Under On Parent Delete, Set NULL sets all foreign key columns in matching rows to null.
  • Do Not Enforce Turns off referential integrity for the relationship.

Note Not all database management systems (DBMSs) support all the options listed above. Refer to the documentation for your DBMS when you set referential integrity options. You should specify the settings that your DBMS supports.

Click to show or hide information. How referential integrity notation is displayed

If you choose to display referential integrity notation, it is displayed next to the relationship lines in your model. The notation is displayed in this form: u:C, where "u" is the type of change (update or delete) and "C" is the type of action (No Action, Cascade, Set Default, or Set NULL).

Referential integrity notation | Triggered action
u | On update
d | On delete
R | No action (that is, Restrict
C | Cascade
D | Set Default
N | Set NULL
(Blank) | Do not enforce referential integrity

Note Microsoft Visio does not support many-to-many relationships in IDEF1X notation.


About the Code Editor (Database model)

To view or edit code in the Code window, open the Code Editor. The Code Editor provides

  • Drag-and-drop text editing.
  • Unlimited number of undo and redo instances.
  • Toolbar buttons that provide easy access to the Cut, Paste, Undo, and Redo commands. (Pause the pointer over a button to see what it does.)
  • Options for changing the window's appearance.

Use the Code Editor to

  • Create stored procedures, functions, or DDL (Data Definition Language) code
  • Create triggers
  • Create check clauses for tables
  • Create check clauses for columns
  • Define views
  • Save code in mirror files

Note As you work in the Code Editor, you can set options to automatically indent code, color keywords, and insert blank code skeletons.


About defining new code (Database model)

You can create the following new code as you work in your database model diagram:

  • Stored procedures, functions, or DDL code
  • Triggers
  • Check clauses for tables
  • Check clauses for columns
  • Views

Note Because not all platforms support these types of code, you need to know the capabilities of your database when you create code for a model.

Click to show or hide information. New global code

To add global code to your model quickly, you use the Code window to create a new stored procedure, function, view definition, or other platform-specific DDL (Data Definition Language) code.

Click to show or hide information. New local code

To create new local code, you work with specific table shapes in your database model diagram. You select the table (entity) shape for which you want to create check clauses or triggers.

Click to show or hide information. New code is platform specific

When you create new code for a model, the code is platform specific. Microsoft Visio associates the code with the database driver currently selected in the Database Drivers dialog box.

You can later transfer the code, or a copy of it, to a different platform.

Click to show or hide information. New code appears as an item in the Code window

After you write the code and define its properties in the Code Editor, it appears as an item in the Code window.

If you later generate a DDL script from your model or create an update script, the new code appears in the script in the order it appears in the Code window.


About code for views (Database model)

You can work directly in the Code Editor to define a view intended for a particular database.

When you define a view in the Code Editor, the view

  • Is associated with the current database driver.
  • Is not visually represented in your diagram.
  • Appears as an item in the Code window.

Click to show or hide information. Reverse engineered views

When you reverse engineer a database that contains views, the views appear in the following windows:

  • Tables and Views window
  • Code window

You can drag views from the Tables and Views window into your diagram, where they are represented by the View shape.

Click to show or hide information. Views for any type of code

Views can represent platform-specific or portable code. Only platform-specific views appear in the Code window.

Click to show or hide information. New views created in the Code Editor

For most target databases, you create a new view in the Code Editor by using the CREATE VIEW statement. Within this statement, you name the view that contains records specified by the SELECT statement.

For example, the following code creates the view MYVIEW that displays the ITEMREQUIRED column from the ORDERS table:

CREATE VIEW MYVIEW AS SELECT ITEMREQUIRED FROM ORDERS;

Click to show or hide information. New views created with the View shape

If you want to create a view that also appears in your diagram, use the View shape.

When you use the View shape, you can

  • Define a simple view for which Microsoft Visio can generate SQL code that defines the view's contents.
  • Overwrite the view's auto-generated code by typing your own SQL code.

When you customize the code for a View shape, the view definition is associated with the currently selected database driver, and the view code appears in the Code window.


About updating shapes in database solutions (Database model)

Some Microsoft Visio solutions, such as Database Model Diagram and the UML Model Diagram, implement some behavior within their solution shapes. For example, some shapes may have special right-click menu items. With each new release, some shapes may have new behavior. For example, a right-click menu item may be added or removed. When a drawing is opened, Visio checks to see if any out-of-date shapes exist, and if some are found, the Update Shapes dialog is automatically launched. We recommended that you update the shapes so that your drawing uses the most current shapes available. If you decide not to update the shapes, you can update the shapes later (on the Tools menu, point to Macros, point to Visio Extras, and click Update Shapes).

Click to show or hide information. When to update shapes

You should update shapes

  • When you open a file that was created in Visio Enterprise 5.0 or Visio 2000 Enterprise Edition.

If Visio detects out-of-date shapes, you are prompted to update the shapes.

  • Any time a file contains out-of-date shapes.

A file can contain out-of-date shapes if you chose not to update shapes when you opened a file created in a previous version.

Click to show or hide information. Choosing not to update shapes

If you choose not to update shapes, the shapes

  • Maintain their behavior from the previous version of Visio.
  • Have the same right-click menus that they had in the previous version of Visio.
  • Might not have the same text formatting.
  • Might not match your documentation.


About data source connections (Database model)

As part of updating a database model diagram, you must specify how to locate and communicate with the database with which you want to work.

Whether you use a desktop or client/server database, you can use the Refresh Model Wizard to

  • Select an installed Microsoft Visio database driver.
  • Associate a 32-bit ODBC (Open Database Connectivity) driver with an installed Visio database driver.
  • Select or create a 32-bit ODBC data source.

For details about creating a data source, see the documentation for the Microsoft ODBC Data Source Administrator.

Click to show or hide information. Connect to a data source before working

You can set up the DBMS connections before you work with the wizards. If you are updating a database or model stored in Microsoft Repository, you must connect to Microsoft Repository by using the Microsoft DBM Repository driver provided with Microsoft Visio.

Click to show or hide information. DDL scripts and ODBC data sources

When you generate a DDL (Data Definition Language) script that performs updates, you do not need an ODBC data source unless your target database requires server information in its CREATE DATABASE statement. The Update Database Wizard detects whether the database requires an ODBC data source and provides options you can choose from.


About database drivers (Database model)

Click to show or hide information. How Microsoft Visio uses database drivers

A database driver is a file that Microsoft Visio uses to communicate with your DBMS to

  • Reverse engineer a database model.
  • Refresh a model from a database.
  • Generate a new database.
  • Update an existing database.

You use Visio database drivers in conjunction with vendor-supplied 32-bit ODBC drivers to connect directly to your DBMS.

Each driver tells Visio

  • How to reverse engineer DBMS-specific information.
  • The kind of DDL (Data Definition Language) script to generate.
  • How to map data types and constraints.
  • How to specify extended attributes for your particular database application.

Click to show or hide information. Microsoft Visio database drivers

Microsoft Visio includes proprietary database drivers that are optimized to provide DBMS-specific functionality. The Visio setup program automatically installs these drivers, which are enhanced to work specifically with the Database Model Diagram template and the Microsoft ODBC Data Access Pack version 3.51 ODBC drivers.

To determine which Visio database drivers you have installed on your system, on the Database menu, point to Options, and then click Drivers.

The Visio setup program automatically selects Microsoft Access as the default Visio database driver.

To create a database model for a different database management system, you set the Visio database driver for the DBMS you are targeting as the default driver.

Click to show or hide information. Visio drivers and 32-bit ODBC drivers

After you have installed and correctly configured the appropriate database software, you can configure the proprietary Visio database driver and associate it with the 32-bit ODBC driver or OLE DB provider for your specific DBMS.

When you configure your database drivers, you

  • Specify a default driver and default mapping for column creation.
  • Associate a 32-bit ODBC driver or OLE DB provider with a particular Visio driver.
  • Configure default settings such as preferred settings for portable data types.

Note For an association between a Visio driver and a 32-bit ODBC driver to work properly, the drivers you select must be designed to work with each other. For example, you would associate a Visio database driver for Oracle with a vendor-supplied ODBC Oracle driver.

When you reverse engineer a database, generate a database, refresh a model, or update a database, Visio provides a filtered list of available data sources based on the database driver associations you established.

Click to show or hide information. Interaction between Visio drivers and 32-bit ODBC drivers illustrated

Interaction between Visio drivers and 32-bit ODBC drivers

To connect to a database, your Visio model (1) uses a Visio database driver (2) to communicate with a Data Source Name (DSN) file and a 32-bit ODBC driver (3).

Click to show or hide information. Before you connect to a DBMS

You must have installed and correctly configured the appropriate database software, client software if necessary, and 32-bit ODBC drivers. If you have difficulty, your system administrator can assist you with

  • Obtaining access and rights to your particular desktop or SQL-based client/server database.
  • Installing and configuring the appropriate client software on your local drive for a client/server database.
  • Installing the correct vendor-supplied 32-bit ODBC drivers or OLE DB providers for your DBMS on your local drive.
  • Using the Microsoft ODBC Data Source Administrator to configure vendor-supplied 32-bit ODBC database drivers correctly.

For details about using the Microsoft ODBC Data Source Administrator to configure and create a data source, see that product's online help.

Click to show or hide information. Supported databases

Visio includes database drivers for the following DBMSs:

  • Microsoft Access 2.0, 7.0, 97, 2000
  • IBM DB2 Universal Database 2.1.2, 5.0
  • INFORMIX-SE 5.0, 6.0, 7.x; INFORMIX-OnLine Dynamic Server 7.x
  • Microsoft SQL Server 6.0, 6.5, 7.0, 2000
  • Oracle 7.x, 8
  • Sybase Adaptive Server 10, 11.x (formerly Sybase System)

If you are developing a database for a DBMS other than one listed here, you can use the

  • ODBC Generic Driver supplied with Visio with a vendor-supplied 32-bit ODBC driver.
  • Generic OLE DB Provider supplied with Visio with a vendor-supplied OLE DB provider.

When you use the generic driver, the information you can reverse engineer, generate, or update depends on the capabilities of the vendor-supplied 32-bit ODBC driver or the vendor-supplied OLE DB provider.

Notes

  • A vendor-supplied 32-bit ODBC driver must be from an ODBC Data Access Pack greater than version 2.0 and ODBC Level 1-compliant or greater.
  • A vendor-supplied OLE DB provider must be compliant with the OLE DB version 1.0 or later specification.


About ODBC data sources (Database model)

After you configure and associate drivers, you can create a data source for a particular database.

A data source includes the data you want to access and crucial information for accessing that data, such as the name of the database, the server on which it resides, and network information. Microsoft Visio refers to a data source by its data source name (DSN).

Note Visio supports only user and system DSNs. It does not support data sources created as file DSNs.

Click to show or hide information. New data sources

You can create a 32-bit ODBC data source when you

  • Start working, by using the Microsoft ODBC Data Source Administrator.
  • Work in the Reverse Engineer Wizard, Generate Wizard, Refresh Model Wizard, or Update Database Wizard.

Each of these wizards contains a screen where you can select connection options and establish ODBC DSNs.

Click to show or hide information. Existing data sources

You can edit and manage data sources you create in Visio as you would any other data source by using the Microsoft ODBC Data Source Administrator.

If you need help setting up, modifying, or removing an ODBC data source for your particular type of database, see the online help that accompanies the Microsoft ODBC Data Source Administrator.


Database model example

Database model example


About mirror files (Database model)

A mirror file is a file separate from your database model diagram in which you can save the code from your diagram.

By creating a mirror file, you create a file of your stored procedure, trigger, or check clause that you can

  • Save.
  • Work with outside of Microsoft Visio.
  • Store using your source code control software.

You can create a mirror file for any code in your database model diagram.

Once you create a mirror file, you can keep it synchronized with your diagram: On the Database menu, point to Code, and then click Synchronize. Then, when you edit the code outside Visio or in the Code Editor, the model and the mirror file are synchronized.


IDEF1X notation with referential integrity notation visible

IDEF1X notation with referential integrity notation visible


Relational notation with data types visible

Relational notation with data types visible


Define a category with the connector tool (Database model)

  1. Open the database model or ER source model diagram.
  2. From the Entity Relationship or Object Relational stencil, drag a Category shape into the diagram.
  3. Click the Connector tool on the Standard toolbar, and then, on either stencil, click the Parent To Category shape. Position the Connector tool over the center of the parent table so that the table is outlined, and then drag to the center of the Category shape. When the Category shape is outlined, release the mouse button.

Both connection points appear red.

  4. With the Connector tool, click the Category To Child shape on either stencil. Position the Connector tool over the center of the Category shape so that it is outlined, and then drag to the center of the child table. When the child table is outlined, release the mouse button.

Both connection points appear red, and the primary keys from the parent table are added as foreign keys to the child table.

  5. Repeat step 4 for each child table.


Define a category (Database model)

  1. Open the database model or ER source model diagram.
  2. From the Entity Relationship or Object Relational stencil, drag a Category shape into the diagram.
  3. Drag a Parent To Category shape onto the drawing page. Drag one end of the Parent To Category shape to the center of the parent table so that the table is outlined, and then release the mouse button. Drag the other end of the Parent To Category shape to the center of the Category shape. When the Category shape is outlined, release the mouse button.

Both connection points appear red.

  4. Drag a Category To Child shape onto the drawing page. Drag one end of the Category To Child shape to the center of the Category shape so that the Category shape is outlined, and then release the mouse button. Drag the other end of the Category To Child shape to the center of the child table. When the child table is outlined, release the mouse button.

Both connection points appear red, and the primary keys from the parent table are added as foreign keys to the child table.

  5. Repeat step 4 for each child table.


Set a Category shape as complete or incomplete (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the Category shape whose properties you want to edit to open the Database Properties window.
  3. In the Categories list, click Definition, and then do one of the following:
  • Uncheck the Category Is Complete box to mark the category as incomplete.
  • Check the Category Is Complete box to mark the category as complete.

The database model diagram is updated automatically.


Specify a category discriminator attribute (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the Category shape whose properties you want to edit to open the Database Properties window.
  3. In the Categories list, click Definition, click This attribute, and then select the column from the parent table you want to define as the category discriminator.

The database model diagram is updated automatically.


Reorder the columns in a table (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the columns you want to reorder, and then click Columns in the Categories list in the Database Properties window.
  3. Click the name of the column whose location you want to change, and then click Move Up or Move Down to move it to a new location in the column list.

The database model diagram is updated automatically.

Click to show or hide information. Tip

To move a series of columns, click the name of the first column you want to remove, hold down the SHIFT key, click the name of the last column you want to remove, and then drag the columns to a new location in the column list.


Remove a user-defined data type (Database model)

  1. Open the database model or ER source model diagram.
  2. On the Database menu, click User Defined Types.
  3. In the User-Defined Types dialog box, select the data type you want to delete.
  4. Click Remove, and then click OK.

Note You can also delete a user-defined type from the Types window.

Click to show or hide information. How?

  1. On the Database menu, point to View, and then click Types to open the Types window.
  2. Right-click the composite type or user-defined type you want to remove, and then click Delete.


Display data types (Database model)

  1. Open the database model or ER source model diagram.
  2. On the Database menu, point to Options, and then click Document.
  3. In the Database Document Options dialog box, click the Table tab, and then, under Data Types, click Show physical or Show portable.
  4. Click OK.


Add a composite data type to a table (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the column to which you want to add a composite data type, and then in the Database Properties window, in the Categories list, click Columns.
  3. Click the name of the column to which you want to add a composite data type, and then click Edit.
  4. In the Column Properties dialog box, click the Data Type tab, and then select the composite data type from the User Defined Type list.
  5. When you are satisfied with your settings, click OK.

Click to show or hide information. Tip

You can also add a composite data type to a table by clicking the Data Type box for the column to which you want to add a composite data type and either clicking the composite data type or typing the name of the composite data type.


Edit the SQL code for a view (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the view whose SQL code you want to edit, and then in the Database Properties window, in the Categories list, click SQL.
  3. Clear the Auto-Generated check box, and then edit the code.

The database model diagram is updated automatically.

Note You can also edit SQL code in the Code Editor. To open the Code Editor, click Code Properties.


Create a foreign key relationship between tables with the connector tool (Database model)

  1. Open the database model or ER source model diagram.
  2. Click the Connector tool [image] on the Standard toolbar. Position the Connector tool over the center of the parent table so that the table is outlined and drag to the center of the child table. When the outline appears on the child table, release the mouse button. Both connection points appear red, and any primary keys in the parent table are added as foreign keys to the child table.
  3. Define the relationship by editing its properties.

Click to show or hide information. How?

  1. Open the database model or ER source model diagram.
  2. Double-click the relationship whose properties you want to edit to open the Database Properties window.
  3. In the Database Properties window, click the category that contains the information you want to edit, and then make your changes.

The database model diagram is updated automatically.


Edit referential integrity settings (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the Relationship shape for which you want to edit referential integrity settings, and then in the Database Properties window, in the Categories list, click Referential Action.
  3. Select options for On Parent Update and On Parent Delete.


Show referential integrity settings (Database model)

  1. On the Database menu, point to Options, and then click Document.

This command is available in the Database Model Diagram and ER Source Model templates.

  2. On the Relationship tab, select Referential Integrity, and then click OK.


Show cardinality notation (Database model)

  1. On the Database menu, point to Options, and then click Document.

This command is available in the Database Model Diagram and ER Source Model templates.

  2. On the Relationship tab, select Cardinality, and then click OK.


Show crow's foot notation (Database model)

  1. On the Database menu, point to Options, and then click Document.

This command is available in the Database Model Diagram and ER Source Model templates.

  2. On the Relationship tab, select Crow's Feet, and then click OK.


Set driver-specific index attributes (Database model)

  1. In the Database Properties window, in the Categories list, click Indexes, and then in the Index name list, select an index.
  2. Click Options to open the Index Attributes dialog box.
  3. Click Edit.

Note If a particular database management system (DBMS) does not support extended attributes, the Edit button is unavailable.

  4. In the driver-specific extended attributes dialog box, fill in the appropriate information, select any options you want, and then click OK.
  5. To close the Index Attributes dialog box, click Close.


Add, remove, or reorder columns in an index (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the index you want to modify, and then in the Database Properties window, in the Categories list, click Indexes.
  3. In the Index Name list, select the name of the index you want to modify.
  4. Do one of the following:
  • To add a column to an index, under Available columns, select the name of the column, and then click Add >.
  • To remove a column from an index, under Indexed columns, select the name of the column, and then click Remove <.
  • To reorder a column, under Indexed Columns, select the name of the column, and then drag the column name to its new position in the list.

The database model diagram is updated automatically.


Define a name space value for an entity or view (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the entity or view whose name space you want to set to open the Database Properties window.
  3. In the Categories list, click Definition, and then type the distinguishing text in the Name space box.

The database model is updated automatically.


Open the Code window (Database model)

On the Database menu, point to View, and then click Code.

If you want to | Do this in the Code window
Reorder code items in the list | Drag an item to a new position in the list.
Remove code from your diagram | Select an item in the list and press DELETE.
Display only global code | Right-click any item and click Show Only Global Code.
Create a new stored procedure, function, view definition, or other DDL code | Click Global Code, and then click New to work in the Code Editor.
View an item in the Code Editor | Select an item in the list and click Edit, or double-click the item.


Open code in the Code Editor (Database model)

  1. If the Code window is not already open, on the Database menu, point to View, and then click Code to open the window.

Note This command is available in the Database Model Diagram or ER Source Model template.

  2. Click a code item in the list, and then click Edit.
  3. To set preferences for working in the Code Editor, do one of the following:
  • Click Window Properties [image].
  • Right-click the code, and then click Properties from the shortcut menu.
  4. In the Window Properties dialog box, select options from the various tabs, and then click OK to return to the Code Editor.
  5. On the Body tab, view and revise code.
  6. On the Properties tab, choose the type of code you are creating or create mirror files.
  7. On the Notes tab, add comments for your code.
  8. To close the Code Editor, click OK.


Work in the Code Editor (Database model)

If you want to | Do this
Automatically indent lines as you enter code, following the scoping rules of SQL | Right-click the code and click Properties. On the Language/Tabs tab, click Follow Language Scoping, and then click OK.
Assign keyboard shortcuts | Right-click the code and choose Properties. On the Keyboard tab, select a command, and then assign a new shortcut.
Change keyword colors in the code | Right-click the code and choose Properties. On the Color/Font tab, select an item, and then select a new foreground or background color.
Print the code for an item | Right-click an item in the list, and then click the Print button on the toolbar.


Define a view (Database model)

  1. If the Code window is not already open, on the Database menu, point to View, and then click Code to open the window.

Note This command is available only in the Database Model Diagram and ER Source Model templates.

  2. Click Global Code, and then click New to open the Code Editor.
  3. On the Properties tab, type a name for the code item. Under Type, select View Definition.
  4. On the Body tab, enter SQL statements to define the view. When you are finished, click OK.

The code item is added to the Global Code list in the Code window.

Note Microsoft Visio generates the code in a DDL script in the order that code items appear in the Code window. Therefore, if you create a view based on another view, make sure the original view appears earlier in the list than the views based on it.


Reorder views in the Code window (Database model)

  1. If the Code window is not already open, on the Database menu, point to View, and then click Code to open the window.

Note This command is available only in the Database Model Diagram and ER Source Model templates.

  2. Drag the view you want to reorder to a new position in the window.


Save code in a mirror file (Database model)

  1. If the Code window is not already open, on the Database menu, point to View, and then click Code.

Note This command is available only in the Database Model Diagram and ER Source Model templates.

  2. In the Code window, select the code you want to save, and then click Edit.
  3. On the Properties tab, under Mirror file, type a path and file name in the File name box, or click Browse to locate the file you want, and then click OK.
  4. In the Code Editor, click OK.


Configure a Microsoft Visio database driver (Database model)

  1. On the Database menu, point to Options, and then click Drivers.

Note This command is available in the Database Model Diagram, the ER Source Model, and the ORM Source Model templates.

  2. In the Database Drivers dialog box, click the Drivers tab. Under Default driver for Visio, select the driver you want to configure.
  3. Click the Default Mapping tab and do the following:
  • Select a category type in the Default category type for column creation list.
  • From the Category list, select a category, and then select the default type, size, and length for the category type that you selected. (Repeat for each category.)
  4. On the Driver Misc tab, specify default settings for DDL script generation used by the Generate and Update Database wizards.
  5. When you finish configuring a database driver, do one of the following:
  • Click the Drivers tab, and then click Setup to associate the driver with a 32-bit ODBC driver.
  • Click OK to close the dialog box.


Set a default Microsoft Visio database driver (Database model)

  1. On the Database menu, point to Options, and then click Drivers.

Note This command is available in the Database Model Diagram, the ER Source Model, and the ORM Source Model templates.

  2. In the Database Drivers dialog box, click the Drivers tab. Under Default driver for Visio, select the driver you want to set as the default.


Associate a Microsoft Visio database driver with a 32-bit ODBC driver (Database model)

  1. On the Database menu, point to Options, and then click Drivers.

Note This command is available in the Database Model Diagram, the ER Source Model, and the ORM Source Model templates.

  2. In the Database Drivers dialog box, click the Drivers tab. Under Default driver for Visio, select the driver with which you want to associate an ODBC driver.
  3. Click Setup.
  4. Click the ODBC Drivers tab, and then select a 32-bit ODBC driver to associate with the Microsoft Visio driver you selected.

You must choose a 32-bit ODBC database driver that is designed to work with your Microsoft Visio database driver. For example, you would associate a Microsoft Visio database driver for Oracle with a vendor-supplied ODBC Oracle driver.

Note The contents of the Preferred Settings tab are driver-specific and reflect the specific features available in the DBMS.

  5. Click OK to close the driver-specific dialog box, and then click OK to close the Database Drivers dialog box.


Associate a Microsoft Visio database driver with a 32-bit ODBC driver in a Visio wizard (Database model)

  1. On the wizard screen where you can select connection options, click Setup.

Note This screen is available in the Reverse Engineer, Generate, Refresh Model, and Update Database wizards.

  2. On the ODBC Drivers tab, select a 32-bit ODBC driver to associate with the Microsoft Visio driver you selected.

You must select a 32-bit ODBC database driver that is designed to work with your Microsoft Visio database driver. For example, you would associate a Microsoft Visio database driver for Oracle with a vendor-supplied ODBC Oracle driver.

Note The contents of the Preferred Settings tab are driver-specific and reflect the specific features available in the DBMS.

  3. Click OK to close the driver-specific dialog box and return to the wizard screen.


Create a new ODBC data source in a Microsoft Visio wizard (Database model)

  1. On the wizard screen where you can select connection options, click New.

Note This screen is available in the Reverse Engineer, Generate, Refresh Model, and Update Database wizards.

  2. Click User Data Source or System Data Source as the type of new data source you want to create, and then click Next.

Note Your Microsoft Visio product supports only user and system Data Source Names (DSNs). It does not support data sources created as file DSNs.

  3. Select the 32-bit ODBC driver you want to use, click Next, and then click Finish.

The 32-bit ODBC driver you select might be associated with one or more Microsoft Visio database drivers.

  4. In the driver-specific dialog box that opens, type a data source name and description, and then do one of the following:
  • Click Select to select an existing database for the DSN, and then provide the required information.
  • Click Create to create a new database for the DSN, and then provide the required information.
  5. When you are finished, click OK.

The ODBC Data Source Administrator creates the data source, and it is added to the list of available data sources on the wizard screen where you select connection options.


Show related tables (Database model)

  1. If the Tables and Views window is not already open, on the Database menu, point to View, and then click Tables and Views.
  2. Drag a table from the Tables and Views window.
  3. Right-click the table, and then click Show Related Tables.

The related tables and relationships appear on the drawing page.


Customize the appearance of a database model (Database model)

  1. Open the database model diagram.
  2. On the Database menu, point to Options, and then click Document.
  3. Choose the options you want in the Database Document Options dialog box.
  4. Click OK.

Note The options you select affect the current document only.


Set behavior options for a database model diagram (Database model)

  1. Open the database model diagram.
  2. On the Database menu, point to Options, and then click Modeling.
  3. On the Logical Diagram tab, choose options that determine the behavior when you remove objects from the diagram, when relationships appear in the diagram, and how table and column names appear.
  4. On the Logical Misc tab, choose the options for foreign keys and default name prefixes and suffixes.
  5. On the Fact Editor tab, do the following:
  • In the Category list, click Input Preferences, and then choose options for guided and freeform input.
  • In the Category list, click Miscellaneous Preferences, and then choose options for fact completeness and general fact editor behavior.
  • In the Category list, click Syntax Colors, and then choose options for color-encoding fact syntax elements.
  6. On the ORM Diagram tab, choose an option for behavior when you remove objects from the diagram, and specify naming conventions for object types.
  7. When you are finished, click OK.

Note The options you select affect the current document and all future documents.


Switch between IDEF1X and Relational notation (Database model)

  1. Open the database model diagram.
  2. On the Database menu, point to Options, and then click Document.
  3. Click the General tab, for Symbol set, click IDEF1X or Relational, and then click OK.


Set a collection type (Database model)

  1. Open the database model or ER source model diagram.
  2. Double-click the table that contains the column you want to specify as a collection type, and then in the Database Properties window, in the Categories list, click Columns.
  3. Click the name of the column you want to set as a collection type, and then click Edit.
  4. In the Column Properties dialog box, click the Collection tab, and then click a collection type.
  5. When you are satisfied with your settings, click OK.


Create a data source by using the ODBC Data Source Administrator (Database model)

  1. In the Windows Control Panel, double-click the ODBC (32bit) icon.
  2. On the User DSN tab in the ODBC Data Source Administrator dialog box, click Add.

Note Your Microsoft Visio product supports only user and system DSNs. It does not support data sources created as file DSNs.

  3. Select the 32-bit ODBC driver you want to use for the data source, and then click Finish.

The ODBC driver you select might be associated with one or more Visio database drivers.

  4. In the driver-specific dialog box that opens, type a data source name and description, and then do one of the following:
  • Click Select to select an existing database for the DSN, and then provide the required information.
  • Click Create to create a new database for the DSN, and then provide the required information.
  5. When you are finished, click OK.

The ODBC Data Source Administrator creates the data source.

  6. Click OK to close the ODBC Data Source Administrator.


Update Database Shapes

  1. Open the drawing that contains the shapes you want to update.
  2. On the Tools menu, point to Macros, point to Visio Extras, and then click Update Shapes.
  3. In the Update Shapes window, select the appropriate check boxes.

Click to show or hide information. Tip

Save a copy of the origianal drawing if you want to open the drawing in a previous version of Microsoft Visio.

  4. Click OK.
  5. Optional: To view a report of the changes made, click Yes when prompted.
  6. On the File menu, click Save to save the newly updated shapes.


Troubleshoot the disappearance of menus (Database model)

Solution-specific menus can disappear when your Microsoft Visio program encounters an unexpected error from either an internal or an external source.

  1. If Visio shuts down unexpectedly, restart it.
  2. On the Tools menu, click Options.
  3. On the Advanced tab, select the Enable Automation events check box, and then click OK.
  4. On the File menu, click Exit.
  5. Re-open the solution.


About DBMS connections (Database model)

Click to show or hide information. Independent database models

You can create a database model independent of its implementation on any particular database management system (DBMS). Because you select a DBMS-specific driver at the time that you connect to your target database, you can implement a database model on more than one DBMS.

For example, you can:

  • Prototype a database that targets one DBMS and implement it on another.
  • Extract a model from a legacy database, reuse the components, and then implement the resulting model on a different DBMS.

Click to show or hide information. Tasks that require you to specify a database driver

You must provide information about the database management system to which you are connecting when you use the:

  • Reverse Engineer Wizard to reverse engineer an existing database.
  • Generate Wizard to generate a new physical database.
  • Refresh Model Wizard to refresh a model from an existing database.
  • Update Database Wizard to update an existing database from a model.

You must specify the following:

  • A Microsoft Visio database driver
  • An associated vendor-supplied 32-bit ODBC (Open Database Connectivity) driver or OLE DB provider
  • A data source


About conceptual and logical models (Database model)

Using the database modeling templates in Visio, you can create conceptual and logical database model diagrams.

Click to show or hide information. Conceptual database model diagrams

Conceptual database model diagrams describe the facts, business rules, and information needs of an organization. They hide specific storage details that are not useful to understanding the basic interrelationships of the stored data. You use the ORM Source Model template to create conceptual database models.

Because conceptual database model diagrams do not map directly to a physical database, before you can generate a relational database schema from a conceptual model, you must have Visio map the conceptual model diagram to a logical model diagram. To do this, add the source model to the project of a Database Model Diagram drawing and build the project. The resulting logical model can be used to generate a relational database schema.

Click to show or hide information. Logical database model diagrams

Logical database model diagrams describe data as it is supported by a relational or object-relational database system. This description includes the relational tables and constraints that apply within and between these tables. You can create logical database models using Relational and IDEF1X notation.

You use the Database Model Diagram template or the ER Source Model template to create logical database models.

In your Microsoft Visio product, logical database model diagrams map directly to physical databases. If you create your logical model diagram using the Database Model Diagram template, you can generate a relational database schema directly from the model. If you create a logical model diagram using the ER Source Model template, you must include your source model in the project of a Database Model Diagram drawing. The collection of source models in the project can be merged (built) into a single logical model, which can then be used to generate a relational database schema.


Database Drivers dialog box (Driver Misc tab)

Use these options to configure your Microsoft Visio database drivers and ODBC drivers. When you configure your Visio database drivers, you specify a default Visio database driver and default data type mapping for column creation, associate a 32-bit ODBC driver with a particular Visio database driver, and configure default settings such as preferred settings for portable data types.

Note A Visio database driveris a Visio-supplied file that allows the Visio program to work better with ODBC and specific database vendor interfaces. For an association between a Visio database driver and a 32-bit ODBC driver to work properly, the database drivers you select must be designed to work with each other. For example, you would associate a Visio database driver for Oracle with a vendor-supplied ODBC Oracle driver.

When you reverse engineer or generate a database, or when you update a model or database, the list of available data sources is based on the Visio database driver associations you establish.

Click to show or hide information. Generate Comments In The Script File

Adds comments from your Visio product to the DDL script generated by the Update Database Wizard or the Generate Wizard.

Click to show or hide information. Do Not Generate

Specifies not to add the following comments—from the Notes tab in the Database Properties window and automatically generated comments—to the DDL script generated by the Update Database Wizard or the Generate Wizard.

Click to show or hide information. Generate Only For Those With User-Specified Notes

Add comments to the DDL script generated by the Update Database Wizard or the Generate Wizard from the text of the Notes tabs in the Database Properties window.

Click to show or hide information. Generate From The User-Specified Notes And Auto-Generate Notes When Unspecified

Adds comments to the DDL script generated by the Update Database Wizard or the Generate Wizard from the text of the Notes tabs in the Database Properties window and from automatically generated comments.


Database Modeling Preferences dialog box (Fact Editor tab)

Note This tab only applies to the ORM Source Models. It does not apply to Database Model Diagrams or ER Source Models.

Click to show or hide information. Category

Specifies which of the following you are setting options for:

  • Input preferences
  • Miscellaneous preferences
  • Syntax colors

Input preferences

Click to show or hide information. Preferred Mode

Specifies which input style the Fact Editor uses: Guided or Freeform.

Click to show or hide information. Use Combined Uniqueness And Mandatory (UM) Questions For Binary Facts

If checked, specifies that questions will be used to create or edit internal uniqueness and/or mandatory constraints in the Constraints tab of the Fact Editor dialog box.

If cleared, a list box of the possible uniqueness, mandatory, and frequency constraints is displayed instead in the Constraints tab.

Note This applies only for binary fact types.

Click to show or hide information. New Fact Mode

Specifies how object names appear when you enter a new fact in freeform input style: [bracketed] or Capitalized.

Click to show or hide information. Capitalize First Letter Of Object Names

Specifies that when you enter a new fact type in guided input style, object type names are automatically capitalized.

Click to show or hide information. Change Role (Relation) Text To Lowercase

Specifies that when you enter a new fact type in guided input style, each word in the predicate will start with a lowercase letter.

Miscellaneous preferences

Click to show or hide information. Close Fact Editor After Each Fact

Specifies that after you finish entering a fact, the Fact Editor closes.

Click to show or hide information. Beep On Syntax Errors

Specifies that when you type a syntax error, the Fact Editor beeps to let you know.

Click to show or hide information. Keep First Object For Subsequent Fact

Specifies that after you enter a fact, the next fact automatically uses the object name you entered for the original fact.

Click to show or hide information. Inverse Required On Binary Facts

Specifies that when you enter a binary fact, you must also enter inverse text. For example:

Customer places an Order
Order is placed by Customer

Click to show or hide information. Examples Required On Fact

Specifies that when you enter a fact in the Fact Editor (on the Fact tab), you must also enter an example (on the Examples tab) before you can complete the fact and close the Fact Editor.

Click to show or hide information. Uniqueness Constraint Required On Fact

Specifies that when you enter a fact, the fact must contain a uniqueness constraint.

Syntax colors

Click to show or hide information. Syntax Element

Specifies the part of the fact you can customize with a special color.

Click to show or hide information. Display In Color

Specifies the color of the text. By default, Visio uses the following color coding.

Syntax Element | Color
Predicate | Blue
Object | Red
Reference Mode | Purple
All other text | Black

Click to show or hide information. Default

Resets customized colors to the default colors used by your Visio product.

Click to show or hide information. Sample

Displays a sample of the colors you've customized.

Click to show or hide information. Color Encode Fact Elements

Select this check box to use the color coding you've defined to display fact syntax elements.

Clear this check box to display all fact syntax elements in black text.


Database Modeling Preferences dialog box (ORM Diagram tab)

Note This tab only applies to the ORM Source Models. It does not apply to Database Model Diagrams or ER Source Models.

Click to show or hide information. Always drop from model

Click to specify that deleting an Object Type, Predicate, or Constraint shape removes the object type, predicate, or constraint from the model, as well its visual representation (that is, the particular Object Type, Predicate, or Constraint shape) from the drawing page.

Click to show or hide information. Drop From Model Only If Last Occurrence On Diagram

Click to specify that deleting an Object Type, Predicate, or Constraint shape removes the object type, predicate, or constraint from the model only if no other visual representation exists.

Click to show or hide information. Never Drop From Model

Click to specify that deleting an Object Type, Predicate, or Constraint shape removes only its visual representation (that is, the particular Object Type, Predicate, or Constraint shape) from the drawing page. An instance of the object type, predicate, or constraint remains in the Business Rules window.

Click to show or hide information. Ask User What To Do

Click to specify that whenever you delete an Object Type, Predicate, or Constraint shape from the diagram, a message box appears asking whether to remove the shape but to retain an instance of the object type, predicate, or constraint in the Business Rules window, or to remove both the shape and the object type, predicate, or constraint it represents from the diagram

Click to show or hide information. Object Type Name

Specifies the default name of the newly created object type.

Click to show or hide information. Append Ordinal

Appends a number to the end of an object type and keeps a running sequence as new objects are created.


Print dialog box (Range/Copies tab)

Click to show or hide information. Printer

Lists the currently selected printer.

Click to show or hide information. Copies

Enter the number of copies of each document you want.

Check Collate Copies to keep all pages of each copy in consecutive order.

Click to show or hide information. Collate Copies

To organize pages when you print multiple copies of a document, check Collate Copies. A complete copy of the document is printed before the first page of the next copy is printed.

Click to show or hide information. All

Print all pages in the document.

Click to show or hide information. Current Page

Print only the page that is active.

Click to show or hide information. Pages

Print a range of pages in the document.

You can specify a series of consecutive pages, or you can specify multiple series and single pages together.

Examples
Series | 3–10
Mixture | 1, 3–6, 8, 11

Click to show or hide information. Print To File

Direct print output to a file, instead of to a printer.

Specify a name for the output file.

This file contains all the printer codes that your print stream would normally contain. To print the file, use the DOS Copy command.

Click to show or hide information. Browse

Click to specify the location where you want to store the file you are going to create.

Do this before you specify a file name. Clicking this button clears any value in the prompt.

Click to show or hide information. Setup

Click to change printer configuration.

You can change the designated printer and some aspects of page formatting, including paper size.


Page Setup dialog box (Size/Type tab)

Use these options to set various printed page characteristics for the report you want to print, such as paper orientation, printer margins, and headers and footers.

Click to show or hide information. Paper Size

Sets the printer paper size for the report.

Click to show or hide information. Width

Displays the paper width for the selected paper size option.

Click to show or hide information. Height

Displays the paper height for the selected paper size option.

Click to show or hide information. Portrait

Uses vertical paper orientation.

Click to show or hide information. Landscape

Uses horizontal paper orientation.

Click to show or hide information. Fit On Paper When Printed

Specifies to scale the report to fit within the selected paper size.

Click to show or hide information. Center On Paper When Printed

Specifies to center the report on the printed page.

Click to show or hide information. Set As Default Size

Makes the current settings the default.


Page Setup dialog box (Margin tab)

Use these options to set various printed page characteristics for the report you want to print, such as paper orientation, printer margins, and headers and footers.

Click to show or hide information. Top

Distance from the top edge of the paper to the first printed material, including header data.

Note Laser printers do not print to the edge of a page.

Click to show or hide information. Bottom

Specifies the distance from the bottom edge of the paper to the first printed material, including footer data.

Note Laser printers do not print to the edge of a page.

Click to show or hide information. Left

Specifies the distance from the left edge of the paper to the first printed material.

Note Laser printers do not print to the edge of a page.

Click to show or hide information. Right

Specifies the distance from the right edge of the paper to the last printed material on the right edge of the page.

Note Laser printers do not print to the edge of a page.

Click to show or hide information. Units Of Measure

Specifies the units used for margin settings.

Click to show or hide information. Draw Border

Specifies to print a border around the body of the report, exclusive of any header and footer information.


Page Setup dialog box (Header/Footer tab)

Use these options to set various printed page characteristics for the report you want to print, such as paper orientation, printer margins, and headers and footers.

Click to show or hide information. Select A Region To Edit By Clicking On The Preview Window

Specifies the area of the page in which you want to add a header or footer, and displays the default setting for that region.

You can also click an area of the Preview page to display different header and footer options.

Click to show or hide information. Left Header

Specifies the text that appears in the selected region.

You can use plain text or a formatting code.

Click to show or hide information. After Macro Expansion

Displays the expanded version of the formatting code specified in the edit box.

Click to show or hide information. Set

Sets the font for the selected header or footer segment.

This font applies only to the selected design element.


Sync Code With Mirror Files dialog box

If you've created a mirror file of your code, you can keep it synchronized with your model using this command. Then, if you edit the code outside of Visio, or if you change it while you are working in the Code Editor, the changes are synchronized between the model and the mirror file.

Click to show or hide information. Differences

Specifies the differences between the mirror file and the model.

Click to show or hide information. Select All

Specifies to select all of the differences so that you can either update the mirror file or the model based on all of the differences.

Click to show or hide information. Update Mirror File(s) Now

Changes the code stored in the mirror file to match the model based on the differences selected in the Differences list.

Click to show or hide information. Update Model Now

Changes the code stored in the model to match the mirror file based on the differences selected in the Differences list.

Click to show or hide information. Refresh

After you update a mirror file or model based on differences selected in the Differences list, click Refresh to see the remaining differences between the mirror file and the model.


Transfer Code dialog box

Use these options to enable a model to support multiple database platforms by transferring code from one platform to another.

You can use this command to move your code, or a copy of it, from one platform to another. As you work in your model, you can switch between platform-specific versions of the code by changing the default driver for the model.

The Code window provides access to only the version of code for the currently selected database driver. You can create as many platform-specific copies of your code as you need.

Click to show or hide information. Move

Specifies that you want to move the code—remove it from the source DBMS and place it in the destination DBMS.

Click to show or hide information. Copy

Specifies that you want to copy the code so that it exists in both the source DBMS and the destination DBMS.

Click to show or hide information. Source DBMS

Specifies the DBMS in which the code currently exists.

Click to show or hide information. Destination DBMS

Specifies the DBMS into which you want to copy or move the code.


Export To ERwin ERX File dialog box

Click to show or hide information. File

Specifies the Microsoft Visio file that you want to export as an ERwin ERX model.

Click to show or hide information. ERwin Version

Specifies the version of ERwin to which you want to export.


Microsoft SQL Server 7.0 Create Database dialog box (Create On tab)

Click to show or hide information. Logical File Name

Displays the logical file names for available SQL Server databases.

Click to show or hide information. Add

Click to create a new logical file name and map it to a physical file name that represents data files for a SQL Server database.

Click to show or hide information. Delete

Click to delete the selected logical file name.

Click to show or hide information. Update

Click to edit the selected logical file name and its definition.

Click to show or hide information. File Description

Displays a description of the selected SQL Server database.


Microsoft SQL Server 7.0 Create Database dialog box (Transaction Log On tab)

Click to show or hide information. Logical File Name

Displays the logical file names for available SQL Server transaction logs.

Click to show or hide information. Add

Click to create a new logical file name and map it to a physical file name that represents data files for a SQL Server transaction log.

Click to show or hide information. Delete

Click to delete the selected logical file name.

Click to show or hide information. Update

Click to edit the selected logical file name and to edit its definition.

Click to show or hide information. File Definition

Displays the statement that will be inserted into the DDL (Data Definition Language) script


Report dialog box ( Preferences tab) {Table report}

Expand all contracted text Show All

Report dialog box ( Preferences tab)

Click to show or hide information. IDEF1X

Uses IDEF1X terminology, such as "entity" and "attribute," in your report.

Click to show or hide information. Relational

Uses Relational terminology, such as "table" and "column," in your report.

Click to show or hide information. Conceptual

Specifies to use the conceptual names of objects in the report.

Click to show or hide information. Physical

Specifies to use the physical names of objects in the report.

Click to show or hide information. Show Annotations

Specifies to include annotations in the report.

Click to show or hide information. Include External Objects

If your model includes external elements such as table or columns from a different model, specifies to include them in the report.

Within the scope of a source model (ER or ORM), elements (tables, columns, and object types) can be defined as external.

For example if you include a fact in your ORM Source model that states "Customer buys Car from Salesperson," Salesperson may be an externally-defined object type. In this case, the full definition of Salesperson will be specified in a different source model.

During the build process, external object types will be resolved, and the resulting logical model will have the complete picture.

Click to show or hide information. Default To

Specifies the type of report you want to create.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Preferences tab) {Statistical report}

Expand all contracted text Show All

Report dialog box (Preferences tab)

Click to show or hide information. Relational

Uses Relational terminology, such as "table" and "column," in your report.

Click to show or hide information. IDEF1X

Uses IDEF1X terminology, such as "entity" and "attribute," in your report.

Click to show or hide information. Include External Tables

If your model includes external tables from a different model, specifies to include them in the report.

Within the scope of a source model (ER or ORM), tables can be defined as external.

For example if you include a fact in your ORM Source model that states "Customer buys Car from Salesperson," Salesperson may be an externally-defined object type. In this case, the full definition of Salesperson will be specified in a different source model.

During the build process, external tables will be resolved, and the resulting logical model will have the complete picture.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Headers tab)

Click to show or hide information. Report Title

Specifies the title of your report. Type any text into this window, or insert a formatting code using the Insert button.

Click to show or hide information. Document Title

Specifies a subtitle for the report. Type any text into this window, or insert a formatting code using the Insert button.

Click to show or hide information. Group Header (Table Reports)

Specifies a subtitle for groups of sorted records in the report. Type any text into this window, or insert a formatting code using the Insert button.

Click to show or hide information. Sample Document Title

Previews the headers as they will appear in the final report.

Click to show or hide information. Default

Restores the text in the Report Title and Document Title boxes to the default values.

Click to show or hide information. Insert

Inserts a formatting code at the current insertion point.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Pagination tab)

Click to show or hide information. Document Separator

Specifies the formatting to use to separate sections of the report.

Click to show or hide information. Continuous

Specifies to number the report pages consecutively from front to back.

If you don't select this option, starts the numbering with 1 for each new source document in a report based on multiple source documents.

Click to show or hide information. Restart At Starting Page Number

Starts the page numbering with 1 for each new source document in a report based on multiple source documents.

If you don't select this option, numbers the report pages consecutively from front to back.

Click to show or hide information. Group Separator

Specifies the formatting to use to separate groups in the report. Set the groups you want on the Sort/Group tab.

Click to show or hide information. Record Separator

Specifies the formatting to use to separate records in the report. All the information about one table or entity, or one object type, is considered a record.

Click to show or hide information. Starting Page Number

Sets the starting page number for the report.

Click to show or hide information. Page Setup

Opens the Page Setup dialog box, where you can change various printed page characteristics.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Sort/Group tab)

Click to show or hide information. Available Sort Keys

Between Available sort keys and Selected sort keys, all options for sorting your report are listed. Available sort keys shows the options for sorting that are currently not applied to your report.

Click to show or hide information. Selected Sort Keys

Between Available sort keys and Selected sort keys, all options for sorting your report are listed. Selected sort keys shows the options for sorting that are currently applied to your report.

Click to show or hide information. Group Sorted Records By

Specifies how to group database records in the report.

Click to show or hide information. Default

Resets the options on the tab to their original settings.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Attributes tab){Types report}

Expand all contracted text Show All

Report dialog box (Attributes tab)

Click to show or hide information. Options

Specifies the attributes you want to include in your report.

Click to show or hide information. Select All

Includes all of the currently displayed attributes in your report.

Click to show or hide information. Deselect All

Excludes all of the currently displayed attributes from your report.

Click to show or hide information. Default

Resets the options on the tab to their original settings.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Attributes tab){Table report}

Expand all contracted text Show All

Report dialog box (Attributes tab)

Click to show or hide information. Category

Lists many categories of attributes that you can include in or exclude from your report.

Click to show or hide information. Attributes Options / Details

Specifies the attributes you want to include in your report.

Click to show or hide information. Select All

Includes all of the currently displayed attributes in your report.

Click to show or hide information. Deselect All

Excludes all of the currently displayed attributes from your report.

Click to show or hide information. Default

Resets the options on the tab to their original settings.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Attributes tab){Constraint Type report}

Expand all contracted text Show All

Report dialog box (Attributes tab)

Click to show or hide information. Check All Attributes To Include For This Report

Specifies the attributes you want to include in your report.

Click to show or hide information. Select All

Includes all of the currently displayed attributes in your report.

Click to show or hide information. Deselect All

Excludes all of the currently displayed attributes from your report.

Click to show or hide information. Default

Resets the options on the tab to their original settings.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Attributes tab){Fact Type report}

Expand all contracted text Show All

Report dialog box (Attributes tab)

Click to show or hide information. Check All Attributes To Include For This Report

Specifies the attributes you want to include in your report.

Click to show or hide information. Select All

Includes all of the currently displayed attributes in your report.

Click to show or hide information. Deselect All

Excludes all of the currently displayed attributes from your report.

Click to show or hide information. Default

Resets the options on the tab to their original settings.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Attributes tab)-Object Type report

Click to show or hide information. Check All Attributes To Include For This Report

Specifies the attributes you want to include in your report.

Click to show or hide information. Select All

Includes all of the currently displayed attributes in your report.

Click to show or hide information. Deselect All

Excludes all of the currently displayed attributes from your report.

Click to show or hide information. Default

Resets the options on the tab to their original settings.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Attributes tab){Supertype report}

Expand all contracted text Show All

Report dialog box (Attributes tab)

Click to show or hide information. Check All Attributes To Include For This Report

Specifies the attributes you want to include in your report.

Click to show or hide information. Select All

Includes all of the currently displayed attributes in your report.

Click to show or hide information. Deselect All

Excludes all of the currently displayed attributes from your report.

Click to show or hide information. Default

Resets the options on the tab to their original settings.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Filter tab){Constraint Type report}

Expand all contracted text Show All

Report dialog box (Filter tab)

Click to show or hide information. Select All Explicit Constraint Types To Be Included

Specifies the constraint types you want to include in your report.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Filter tab){Fact Type report}

Expand all contracted text Show All

Report dialog box (Filter tab)

Click to show or hide information. All facts in document(s)

Specifies to include all facts in the entire document in your report.

Click to show or hide information. Facts With Object ()

Specifies to include all facts that reference the selected object.

Click to show or hide information. All Facts Matching Filter

Specifies to include all facts that match the selected filters, such as creating a report of all Binary Facts.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Filter tab){Object Type report}

Expand all contracted text Show All

Report dialog box (Filter tab)

Click to show or hide information. All Objects In Document(s)

Specifies to include all object types in the entire document in your report.

Click to show or hide information. All Objects Matching Filter

Specifies to include all object types that match the selected filters, such as creating a report of all subtypes.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (Selection tab){Types report}

Expand all contracted text Show All

Report dialog box (Selection tab)

Click to show or hide information. Composite Types

Specifies to include composite types in your report.

Click to show or hide information. User Defined Types

Specifies to include user-defined types in your report.

Click to show or hide information. Built-In Types

Specifies to include built-in types in your report.

Click to show or hide information. Include External Types

If your model includes external table or columns from a different model, specifies to include them in the report.

Within the scope of a source model (ER or ORM), types can be defined as external.

During the build process, external types will be resolved, and the resulting logical model will have the complete picture.

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Preview

Displays a preview of your report. This feature will not work if you do not have a printer driver installed.

Click to show or hide information. Export To RTF

Opens the Export To RTF dialog box, in which you can save your report in rich text format (RTF), which can be opened in most word-processing programs.


Report dialog box (preview)

Click to show or hide information. Print

Opens the Print dialog box, in which you can set options for printing your report.

Click to show or hide information. Next Page

Displays the next page of the report.

Click to show or hide information. Prev Page

Displays the previous page of the report.

Click to show or hide information. Two Page/One Page

Changes the preview from one page to two pages or from two pages to one page.

Click to show or hide information. Zoom In

Click to see a portion of the report up close.

Click to show or hide information. Zoom Out

Click to see more of the report.


Generate a DDL script (Database model)

  1. On the Database menu, click Generate to open the Generate Wizard.

Note This command is available only in the Database Model Diagram template.

  2. On the first page, select the Generate a text file of the DDL script check box, enter a path and file name for the new script in the File name box, and then click Next.
  3. On the second page, do the following:
  • Select the Microsoft Visio database driver for your DBMS.

If you have not already associated the Visio database driver with a particular 32-bit ODBC data source, do so now.

  • Enter a database, directory, file, or schema name.
  • When you are satisfied with the settings, click Next.
  4. On the third page, review the list of tables and views that will be created in the new database, and click Next to continue.

Note To make changes to the model, click Cancel, and then make changes in the diagram.

The Generate Wizard validates the database schema.

  5. On the last page, review any warnings that resulted during the validation of the database schema. Click Finish to create the DDL script, or click Cancel to return to the Output window and correct any errors or warnings.
  6. If you want to review the script immediately after it has been created, click Yes when you are prompted to view the generated DDL script.

Review the script carefully before you run it in your DBMS. You can open, review, and edit the script in any text editor.

Note If you modified and then ran the DDL script that Visio generated, you must synchronize the tables in the model with the tables in the new database.


Edit a DDL script immediately after you generate it (Database model)

  1. Generate a DDL script by following the instructions in the Generate Wizard.
  2. After you click Finish, you will be asked if you want to view the DDL script. Click Yes.

The DDL script opens in a text editor where you can view or edit the document.

  3. When you have reviewed the code and made any changes, on the File menu, click Save to save your changes.
  4. On the File menu, click Exit to close the text editor.

Note If you modified and then ran the DDL script that your Visio generated, you must synchronize the tables in the model with the tables in the new database.


Edit a DDL script at any time (Database model)

From any text- or code-editing application, open the DDL script you want to view or edit.

Note If you modified and then ran the DDL script that your Microsoft Visio product generated, you must synchronize the tables in the model with the tables in the new database.


Open a file from the Project window (Database model)

  1. Open an existing project file, or create a new one.
  2. If the Project window is not open, on the Database menu, point to View, and then click Project to open the window.
  3. Do one of the following:
  • Double-click the name of the file in the Project window.
  • Right-click the file name, and then click Open Document.


Set properties for an ORM source model

  1. Open an existing ORM source model file, or create a new one.
  2. On the Database menu, point to Options, and then click Document.
  3. In the ORM Document Options dialog box, choose options on the various tabs. For example, if a company requires standard naming conventions for tables, you can specify these settings on the Prefix tab.
  4. When you are satisfied with the settings, click OK.

Note The options you select affect how your ORM source model maps to the collective model. If you built the project's model before you set these options, you must build the model again for the settings to take effect.


Set properties for an ER source model

  1. Open an existing ER source model file, or create a new one.
  2. On the Database menu, point to Options, and then click Document.
  3. In the Database Document Options dialog box, specify the options for your database model project.
  4. When you are satisfied with the settings, click OK.

Note The properties you set for an ER source model file apply to that file only.


Define a table or view as external (Database model)

  1. Open the ER source model to which you want to add an external table or view.
  2. Drag an Entity shape or View shape onto the drawing page.
  3. Double-click the Entity shape or View shape that you want to define as external, and then, in the Database Properties window, under Categories, click Definition.

Click to show or hide information. Tip

If the Database Properties window is hidden, move your pointer over the Database Properties title bar to make the window reappear.

  4. Define the element so that it has the same name and properties as another element in a different source model, and then click the External checkbox.

The ER source model is updated automatically. References to external entities are resolved when you build the project's collective model.

Note Setting a table as external does not automatically make the columns in that table external. If there are columns in another table with the same definition, the project will not build. To avoid this problem, specify any columns in the external table that share definitions with other columns as external.


Define a column as external (Database model)

  1. Double-click the entity shape that contains the column you want to define as external, and then in the Database Properties window, under Categories, click Columns.

Click to show or hide information. Tip

If the Database Properties window is hidden, move your pointer over the Database Properties title bar to make the window reappear.

  2. Select the name of the column you want to define as external, and then click Edit.
  3. In the Column Properties dialog box, on the Definition tab, select the External check box, and then click OK.

References to external entities are resolved when you build the project's collective model.


Correct an error or warning (Database model)

  1. In the Output window, double-click the error or warning text.

Visio selects the element that contains the error in the diagram.

  2. Make the necessary corrections to your diagram.
  3. Repeat steps 1 and 2 for each error or warning in the Output window.


Validate your model (Database model)

  1. Do one of the following:
  • If you are using the Database Model Diagram template, on the Database menu, point to Model, and then click Error Check.
  • If you are using the ER Source Model or ORM Source Model template, on the Database menu, click Model Error Check.
  2. In the Output window, double-click an error or warning to highlight the source of the problem in the diagram.
  3. Correct the cause of the error or warning.
  4. Repeat steps 2 and 3 for each error or warning in the Output window.


Export a report (Database model)

  1. Generate a report.
  2. In the Report dialog box, click Export to RTF.
  3. In the Export To RTF dialog box, type a file name and path, and then click Save.

Microsoft Visio exports the report as an RTF file and displays a progress indicator in the status bar.

  4. You can open and edit the RTF file in any application that supports RTF files, such as Microsoft Word.

Note Visio exports graphic elements in a report as bitmaps, and exports example data in a tabular format as part of the report.


About transferring code to a new target DBMS (Database model)

You can use a single database model that contains code to generate a database schema for multiple database management systems (DBMSs). Using the Transfer command, you can copy or move code from one DBMS to another. When you use the Transfer command, Microsoft Visio moves or copies all the code in the diagram as text without performing any changes. You can create as many system-specific copies of your code as you need.

For example, in the Transfer Code dialog box you could select Microsoft SQL Server as the Source DMBS and Oracle Server as the Destination DBMS. As you work in your database model diagram, you can switch between specific versions of the code by changing the default driver for the diagram.

The Code window displays code that's specific to a certain DBMS, which depends on the currently selected database driver. Be sure that the default driver for Visio is set to the correct database management system when viewing or editing your code. From the Code window, you can open the Code Editor to modify code as needed.

Note When you transfer code to a different DBMS (switching between code implementations by changing database drivers), you may need to edit your code before it will work on the system selected as the Destination DBMS in the Transfer Code dialog box.


Transfer code (Database model)

  1. On the Database menu, point to Code, and then click Transfer.
  2. In the Transfer dialog box, under Operation, do one of the following:
  • Click Move to transfer all the code to the new platform.
  • Click Copy to transfer a copy of the code, leaving the original implementation as it is.
  3. Under Source DBMS, select the DBMS in which the code currently exists.
  4. Under Destination DBMS, select the DBMS into which you want to copy or move the code.
  5. Click OK.

When the transfer is complete, a message appears on the Info tab of the Output window.


Switch database drivers for the target DBMS (Database model)

  1. On the Database menu, point to Options, and then click Drivers.
  2. In the Database Drivers dialog box, click the Drivers tab.
  3. Under Default driver for Visio, select the driver for the database management sytem to which you transferred the code.
  4. Click OK.

The list in the Code window is updated to display a copy of the system-specific code.


About scripts that update databases (Database model)

Click to show or hide information. What the DDL (Data Definition Language) script is for

Using the Update Database Wizard, you can generate a DDL script. With a DDL script you can update and physically alter a database that is based on a database model diagram.

Click to show or hide information. Contents of the script

When the wizard connects to the target database, it uses the following information to generate the appropriate DDL and, if necessary, DML (Data Manipulation Language) statements in a text file:

  • Server name
  • Database name
  • Other database-specific information

Based on your model diagram, the wizard generates a text file of DDL statements to create, modify, and delete database objects such as tables, columns, and indexes. The script's exact content depends on the target database driver, which tells the wizard:

  • The type of script to create.
  • The data types that are supported.
  • Other database-specific options.

You can review the script, edit it if you want, and execute it later from within your database application.

Note Before you run the DDL script in your database application, open it in a text editor to review it for errors.

Click to show or hide information. DDL script customization

When you configure a Visio database driver, you can customize how the wizard:

  • Generates comments in the DDL script it creates.
  • Creates the SQL statements in the update script.


Create an update script (Database model)

  1. On the Database menu, click Update.

Note This command is available only in the Database Model Diagram template.

  2. On the first wizard page, do the following:
  • Select the Generate a text file with the DDL script check box, and then specify a file name and path for the script.
  • Select the Detect changes in the database check box.
  • Select the Log conflicts to file check box and specify a file name and path. The wizard will write discrepancies to this file.
  • When you are satisfied with your settings, click Next.
  3. On the second wizard page, do the following:
  • Select the Microsoft Visio database driver for your DBMS.

If you have not already associated the Visio database driver with a particular 32-bit ODBC data source, do so now.

  • Select the data source of the database you're updating.

If you have not already created a data source for the existing database, do so now.

When you create a new source, its name is added to the Data Sources list.

  • When you are satisfied with your settings, click Next.
  4. Follow the instructions in any driver-specific dialog boxes that appear.

For example in the Connect Data Source dialog box, type a user name and password, and then click OK.

  5. On the next page, select an item in the list of conflicts, and then select the resolution option you want to use:
  • No change does not change either the model or the database.
  • Refresh model changes the model to match the database.
  • Update database changes the database to match the model.
  6. The next page lists updates to the model diagram that you specified.

For example, if your model diagram contains a table that is not in the database, and you instructed the wizard to update the diagram, the table will be deleted from the diagram.

If you are satisfied with your changes, click Next; if you are not satisfied with your changes, click Back.

  7. The next page lists updates to the database schema that you specified. Review the changes, and then click Next.

For example, if the diagram contains a table that is not in the database, and you instructed the wizard to update the database, the table will be added to the database.

  8. On the final page, click Finish to accept the changes and create an update script.

The wizard checks the diagram for semantic and implementation validity and writes a list of progress notes, errors, and warnings in the Output window.

  • If validation succeeds, the wizard prompts you to save the script in the file you defined on the first page of the wizard.
  • If validation fails, click Cancel to exit the wizard. (Validation fails, for example, if a database table's primary key has been removed.) Correct the errors before you run the Update Database Wizard again.
  9. To view the script immediately after it is created, click Yes when prompted.

Note The Update Database Wizard only creates a DDL script; it does not implement it. You must run the script manually from within your database application.


Generate Wizard

Click to show or hide information. About this wizard

Provides options for generating a DDL (Data Definition Language) script, with instructions for generating a database based on your model or for connecting to a database and generating the contents of your model.

The Generate Wizard creates a database schema for a specific database management system (DBMS) by gathering the information from the database model, checking the model for errors, checking the model's semantics for compliance with DBMS-specific requirements, and mapping the validated model to an optimally normalized database schema.

Click to show or hide information. Generate A Text File Of The DDL Script

Specifies to generate a text file that contains all DBMS-specific code (generally referred to as DDL) necessary to create a physical representation of your database model. Any constraints that cannot be translated directly from the model are noted in the DDL script file. If you do not select the Generate New Database option, after the wizard creates the script, you must manually run the DDL script from within your database application.

Click to show or hide information. File Name

Sets the file name and path of the DDL script created by the wizard. Click Browse to open the Save As dialog box.

Click to show or hide information. Generate New Database

Specifies to connect to your target database and automatically generate a database schema based on the contents of your model. This option is available only if your server supports the direct creation of a database by means such as 32-bit ODBC drivers.

Click to show or hide information. Store Current Database Image In Model

Stores internally an image of the generated database that is later used for comparison when you update a model (by choosing Database, then clicking Update).

Uncheck this option to prevent Visio from storing an image of the generated database. The next time you use the Update Database Wizard, the model appears to Visio as if it had never been generated into a database.


Generate Wizard

Click to show or hide information. About this wizard

Provides options for generating a DDL (Data Definition Language) script, with instructions for generating a database based on your model or for connecting to a database and generating the contents of your model.

The Generate Wizard creates a database schema for a specific database management system (DBMS) by gathering the information from the database model, checking the model for errors, checking the model's semantics for compliance with DBMS-specific requirements, and mapping the validated model to an optimally normalized database schema.

Click to show or hide information. Installed Visio drivers

Displays the default Visio database driver.

Click to show or hide information. Setup

Displays the Setup dialog box, where you can associate the selected Visio driver with a 32-bit ODBC driver.

Click to show or hide information. Create <type> File

Specifies to generate a new database. The name of this option changes depending on the drivers you have selected. If a driver does not support direct creation of a new database, this option is unavailable.

Click to show or hide information. <type> File Already Exists

Specifies to update an existing database. The name of this option changes depending on the drivers you have selected.

Click to show or hide information. Data Source Name

Specifies the name of the new data source to be established on your system when you are creating a new database. A data source name resides on your system, but can apply to databases on other devices.

Click to show or hide information. New

Displays the Create A New Data Source dialog box, where you can define a new data source.

Click to show or hide information. <type> File Name / Directory Name / Database Name

Names the new file, database, directory, or schema.

Click to show or hide information. Extract Server Information

Lists devices on the server that you can select and specify a size for. The wizard then adds this information to the CREATE DATABASE statement it generates.

This check box appears only when the installed Visio driver is Microsoft SQL Server.

If this box is unchecked, the new database created on the default device has the default size.


Generate Wizard

Click to show or hide information. About this wizard

Provides options for generating a DDL (Data Definition Language) script, with instructions for generating a database based on your model or for connecting to a database and generating the contents of your model.

The Generate Wizard creates a database schema for a specific database management system (DBMS) by gathering the information from the database model, checking the model for errors, checking the model's semantics for compliance with DBMS-specific requirements, and mapping the validated model to an optimally normalized database schema.

Click to show or hide information. Data Sources

Lists the available DSNs. Select the DSN for your database from the list.

Click to show or hide information. New

Displays the Create A New Data Source dialog box, where you can define a new data source. The new data source will appear in the Data Sources list.


Generate Wizard

Click to show or hide information. About this wizard

Provides options for generating a DDL (Data Definition Language) script, with instructions for generating a database based on your model or for connecting to a database and generating the contents of your model.

The Generate Wizard creates a database schema for a specific database management system (DBMS) by gathering the information from the database model, checking the model for errors, checking the model's semantics for compliance with DBMS-specific requirements, and mapping the validated model to an optimally normalized database schema.

Click to show or hide information. List

Displays the proposed additions and updates.


Generate Wizard

Click to show or hide information. About this wizard

Provides options for generating a DDL (Data Definition Language) script, with instructions for generating a database based on your model or for connecting to a database and generating the contents of your model.

The Generate Wizard creates a database schema for a specific database management system (DBMS) by gathering the information from the database model, checking the model for errors, checking the model's semantics for compliance with DBMS-specific requirements, and mapping the validated model to an optimally normalized database schema.

Click to show or hide information. Warnings

Displays potential conflicts. See the Output window for additional warnings.


Update Database Wizard

Click to show or hide information. About this wizard

Steps you through connecting to a database, by first refreshing the model with any changes in the database, and then allowing you to propagate any model differences to the database.

To update the database, you can directly alter the database or generate a DDL (Data Definition Language) script that performs the changes.

Click to show or hide information. Generate A Text File With The DDL Script

Specifies to generate a text file that contains all DBMS-specific code (usually SQL) necessary to create a physical representation of your database model. After the wizard creates the script, you must manually run it from within your database application if you have not directly altered the database within the wizard.

Click to show or hide information. File Name

Sets the file name and path of the DDL script created by the wizard. Click the Browse button to open the Save As dialog box.

Click to show or hide information. Update The Database

Specifies to directly connect to the target database and incorporate into it all the changes that your model reflects in its current structure.

Click to show or hide information. Detect Changes In The Database

Specifies to refresh the current model from the database.

Click to show or hide information. Log Conflicts To File

Creates a text file that logs all conflicts between the model and database discovered by the wizard.


Update Database Wizard

Click to show or hide information. About this wizard

Steps you through connecting to a database, by first refreshing the model with any changes in the database, and then allowing you to propagate any model differences to the database.

To update the database, you can directly alter the database or generate a DDL (Data Definition Language) script that performs the changes.

Click to show or hide information. Installed Visio drivers

Displays the default Visio database driver.

Click to show or hide information. Setup

Displays the Setup dialog box, where you can associate the selected Visio driver with a 32-bit ODBC driver.

To modify the configuration for a driver, select the driver, and then click Setup.

Click to show or hide information. Data Sources

Lists the available data sources based on the database driver associations you establish.

Click to show or hide information. New

Displays the Create A New Data Source dialog box, where you can define a new data source.


Update Database Wizard

Click to show or hide information. About this wizard

Steps you through connecting to a database, by first refreshing the model with any changes in the database, and then allowing you to propagate any model differences to the database.

To update the database, you can directly alter the database or generate a DDL (Data Definition Language) script that performs the changes.

Click to show or hide information. List

Lists the differences between the current state of the database and the database image stored in the model. To resolve a conflict, select it in the list, and then choose one of the following:

  • No change
  • Refresh model Changes the model to match the database.
  • Update database Changes the database to match the model.


Update Database Wizard

Click to show or hide information. About this wizard

Steps you through connecting to a database, by first refreshing the model with any changes in the database, and then allowing you to propagate any model differences to the database.

To update the database, you can directly alter the database or generate a DDL (Data Definition Language) script that performs the changes.

Click to show or hide information. List

Displays the proposed changes that will be made to the model.

You can expand the branches of the tree to see details.


Update Database Wizard

Click to show or hide information. About this wizard

Steps you through connecting to a database, by first refreshing the model with any changes in the database, and then allowing you to propagate any model differences to the database.

To update the database, you can directly alter the database or generate a DDL (Data Definition Language) script that performs the changes.

Click to show or hide information. List

Displays the proposed changes that will be made to the database.

You can expand the branches of the tree to see details.


Update Database Wizard

Click to show or hide information. About this wizard

Steps you through connecting to a database, by first refreshing the model with any changes in the database, and then allowing you to propagate any model differences to the database.

To update the database, you can directly alter the database or generate a DDL (Data Definition Language) script that performs the changes.

Click to show or hide information. Warnings

Displays potential conflicts. See the Output window for additional warnings.


New Report Wizard

Click to show or hide information. About this wizard

Creates customizable reports that describe your database model by displaying data definitions and formatting information. For example, you can create a report that documents your database design to present to a client.

You can create reports that summarize file and database statistics or that document the tables in your model. In addition, if you are creating an object relational model, you can report on data types.

Click to show or hide information. Choose The Type Of Report

Lists report types available for the model.

Click to show or hide information. Description

Describes the selected report's contents.


New Report Wizard

Click to show or hide information. About this wizard

Creates customizable reports that describe your database model by displaying data definitions and formatting information. For example, you can create a report that documents your database design to present to a client.

You can create reports that summarize file and database statistics or that document the tables in your model. In addition, if you are creating an object relational model, you can report on data types.

Click to show or hide information. List

Summarizes your choices in the wizard. If this information is not correct, click Back or Cancel.


Database Properties for Entity and View shapes (Definition)

Use these options to specify physical and conceptual names for the table and whether to synchronize the names as you work on this tab.

You can specify a name space value for the table to distinguish it from similarly named tables in the model.

This category also displays information reported by the host database management system (DBMS) about the database owner and the path of the source database. The Defining type box is available only when a table is empty; it lists all composite data types so that you can create a typed table.

Click to show or hide information. Physical name

Type the name of the table as it appears in the database.

Click to show or hide information. Conceptual name

Type the name of the table as it appears in conceptual modeling.

Click to show or hide information. Name space

Type a value that can be used to additionally distinguish between otherwise identically named model elements, such as elements in Oracle databases.

Click to show or hide information. Owner

Shows read-only information about the selected table's owner reported by the host DBMS during the reverse engineering process.

Click to show or hide information. Source database

Shows the database from which the selected table was extracted as reported by the host DBMS during the reverse engineering process. The value is read-only.

Click to show or hide information. Defining type

Lists all composite data types so that you can create a typed table for an object relational database. The Defining type box is available only when a table is empty.

Click to show or hide information. Sync names when typing

Select this check box to synchronize the conceptual and physical names when you change either one.


Database Properties for Entity, Type, and View shapes (Columns/Fields)

Click to show or hide information. Physical Name column

Type the name of the column.

Click to show or hide information. Data Type column

For the Entity shape, click the type of data that will be entered in the column. For object relational databases, type the complex type.

Click to show or hide information. Req'd column

For the Entity shape, select this check box to specify that the column cannot contain NULL values.

Click to show or hide information. PK column

For the Entity shape, select this check box to specify that the column is a primary key for the table.

This check box is not available for the View and Type shapes.

Click to show or hide information. Add

Click to add a column.

Click to show or hide information. Remove

Click to delete the selected column or columns.

Click to show or hide information. Edit

Click to open the Column Properties dialog box, in which you can set additional options for the selected column.

Click to show or hide information. Move Up

Click to reorder the columns in this list and in your diagram.

Click to show or hide information. Move Down

Click to reorder the columns in this list and in your diagram.

Click to show or hide information. Portable data type

Click to show the data types that are not driver-specific.

Click this option when you want to forward engineer your diagram to multiple DBMSs.

Click to show or hide information. Physical data type

Click to show the data type used by the driver you selected.


Database Properties for Entity shape (Primary ID)

Use these options to edit, define, or delete primary keys from a list of available columns and to specify whether to create an index on primary keys.

When you first display this category, you can view any primary key data about the table. You can then use this category to create or change the constitution of the primary key or its physical name, as well as to add an index to the primary key.

Click to show or hide information. Available columns

Lists all columns in the current table that you can use to compose the primary key.

Click to show or hide information. Generate as

Click the appropriate option to specify whether the column should be used as a primary key, alternate key, or non-unique index.

Note Your Microsoft Visio product requires that a key be specified to pass logical validation. Click No Key to allow a model to pass logical validation as if the column were a key, without having to generate an actual key in the database.

Click to show or hide information. Create index

Select this check box to enable an index on the primary key.

Most database systems require an index on each primary key. An index is often associated with the physical name of the primary key.

Click to show or hide information. Add >

Click to move the selected column from the Available columns list to the Primary ID columns list.

Click to show or hide information. < Remove

Click to move the selected component of the primary key from the Primary ID columns list to the Available columns list.

Click to show or hide information. Remove all

Click to move all components of the primary key from the Primary ID columns list to the Available columns list.

Click to show or hide information. Primary ID columns

Lists columns that are components of the primary key.

Click to show or hide information. Physical name

Type the name of the primary key as required by the target database.

The physical name should be a generic name; for example, PrimaryKey or PrimaryKey3. It is specific to the target database and must comply with its requirements.


Database Properties for Entity shape (Indexes)

Use these options category to define an index and to edit its properties for the selected table.

For example, you can edit the name of an index, change index options, rearrange the order of columns in an index, or set extended attributes for an index. Extended attributes for an index are driver-specific and are not supported by all DBMSs.

Microsoft Visio automatically creates unique indexes on primary keys.

Click to show or hide information. Index name

Click an existing index that you want to edit.

Click to show or hide information. Index type

Click an option to specify whether the index is a

  • Non-unique index only
  • Unique index with constraint on top
  • Unique index only
  • Unique constraint only

Click to show or hide information. New

Click to open the New Index dialog box in which you can type a name for the selected index.

Click to show or hide information. Delete

Click to delete the selected index.

Click to show or hide information. Rename

Click to open the Rename Index dialog box, in which you can rename the selected index.

Click to show or hide information. Options

Extended attributes Lists index attributes that have been modified by means of this utility.

Default driver Lists the default Visio database driver associated with an ODBC driver.

Click to show or hide information. Available columns

Lists columns that can be included in an index definition.

Click to show or hide information. Add >

Click to move the selected columns in the Available columns list to the Indexed columns list.

Click to show or hide information. < Remove

Click to move the selected columns from the Indexed columns list to the Available columns list.

Click to show or hide information. Remove all

Click to move all columns from the Indexed columns list to the Available columns list.

Click to show or hide information. Indexed columns

Lists columns that are included in the selected index.


Database Properties for Entity shape (Triggers and Check)

Use these options to add, edit, remove, or preview the code for triggers and check clauses. The options in this category may vary depending on your specific database management system (DBMS).

When you reverse engineer a database that contains code, or when you add new code to a table in your model, the code is shown in this category and is listed under Local Code in the Code window.

Code is included in the DDL (Data Definition Language) script created when you use the Generate or Update Database Wizard.

You can execute a trigger when specific database events occur, such as when a record is inserted, updated, or deleted. For example, you can use a trigger to log audit information to a separate table, calculate derived information such as the extended cost of price times quantity, notify logged-on users of limits that have been exceeded, or call a stored procedure.

Click to show or hide information. Triggers / Check clauses

Lists the code associated with the selected Entity shape.

Click to show or hide information. Preview

Displays approximately four lines of code for the selected trigger or check clause.

Click to show or hide information. Add

Click to open the Code Editor, in which you can write new code. You can resize the Code Editor if you want to see more of your code.

Click to show or hide information. Remove

Click to delete the selected trigger or check clause.

Click to show or hide information. Edit

Click to open the Code Editor and display the code for the selected trigger or check clause. You can resize the Code Editor if you want to see more of your code.


Database Properties for Entity, Type, and View shapes (Extended)

Use these options to specify DBMS-specific extended attributes for use with the Visio database drivers.

Extended attributes are specific to a target DBMS, and allow you to control more precisely the way objects defined in your model are mapped to a target database.

Extended attributes are driver-specific and are not supported by all DBMSs.

Note Unless you are the database administrator or have the equivalent permissions, for best results, do not change the extended attribute settings.

Click to show or hide information. Default driver

Lists the default Visio database driver that was associated with the ODBC driver.

Click to show or hide information. Extended attributes

Lists all extended attributes that have been modified through this utility.


Database Properties for Entity and View shapes (Notes)

Click to show or hide information. Notes

Type notes. The notes you type here are included in the logical model.


Database Properties for Category to Child and Relationship shapes (Definition)

Use these options to associate columns in the parent and child tables and to enter role names for foreign keys.

Relationships indicate visually how the tables in your database model interact with one another, and editing their properties enables you to control how data is stored and retrieved in the database.

Click to show or hide information. Unknown parent

Lists columns in the parent table.

Click to show or hide information. <--Associate-->

Click to associate the two columns selected in the parent and child lists, creating a relationship.

Click to show or hide information. Unknown child

Lists columns in the child table.

Click to show or hide information. Foreign key role name

For IDEF1X models, type the role name defined by the foreign key relationship.

You can use this name to clarify the business rule that is reflected in this foreign key relationship.


Database Properties for Category to Child and Relationship shapes (Name)

Use these options to define the text that appears in the Verbalizer window describing the roles performed by the model's objects.

Role text is read in the direction of parent to child. The verb phrase describes the parent's role; the inverse phrase describes the child's role.

You can also specify the physical name of this relationship and provide descriptive text.

Click to show or hide information. Verb phrase

Type the parent's role.

Role text appears in the Verbalizer window to clarify the relationship between the two tables.

The text is not used to name any database objects.

Click to show or hide information. Inverse phrase

Type the child's role.

Role text appears in the Verbalizer window to clarify the relationship between the two tables.

The text is not used to name any database objects.

Click to show or hide information. Physical name

Type the name of the relationship as it appears in the physical database.

This value is used differently depending on the target database management system (DBMS). For many systems, this value is the name of the foreign key constraint, as defined in an SQL ADD CONSTRAINT clause.

Click to show or hide information. Notes

Type notes about the relationship.

The text you type appears in the Verbalizer window.


Database Properties for Category to Child and Relationship shapes (Miscellaneous)

Use these options to specify the type of cardinality, whether the relationship is identifying or non-identifying, and whether the relationship is optional.

Note To display cardinality settings for a model that uses IDEF1X notation, select the Cardinality (IDEF1X) check box on the Relationship tab of the Database Document Options dialog box.

Click to show or hide information. Zero or more

For IDEF1X models, click to specify that zero or more instances of an entity will be related.

Click to show or hide information. One or more

For IDEF1X models, click to specify that at least one instance of an entity will be related.

Click to show or hide information. Zero or one

For IDEF1X models, click to specify that there can be no more than one instance of the entity in this relationship.

Click to show or hide information. Exactly one

For IDEF1X models, click to specify that there is exactly one instance of the entity in this relationship.

Click to show or hide information. Range

For IDEF1X models, click to specify that no less and no more than the number of entity instances shown in the At least and At most boxes, respectively, can be related.

At least For IDEF1X models, type the minimum number of entity instances that can be related.

At most For IDEF1X models, type the maximum number of entity instances that can be related.

Click to show or hide information. Identifying

Click to specify an identifying relationship, where the foreign key relationship includes a primary key component in the child table.

Part of the primary key of the child table is also part of the primary key of the parent table.

Click to show or hide information. Non-identifying

Click to specify a non-identifying relationship, where the foreign key relationship does not include a primary key component in the child table.

The part of the parent table primary key that is shared by the child table is not part of the child table primary key.

Click to show or hide information. Optional

Select this check box to specify that NULL values are possible in the connected attribute.

NULL values are only possible if the relationship is non-identifying (not part of a primary key).


Database Properties for Category to Child and Relationship shapes (Referential Action)

Use these options to determine the effect on the child table when information in the parent table is updated or deleted.

You can make exceptions to the general referential integrity policies you have set for the database at large. The options you set affect only the selected relationship. Visio automatically generates referential integrity constraints between related tables using the settings you define.

On parent update

Click to show or hide information. No action

Select to make no automatic changes to the foreign key when the parent in the selected relationship is updated. This is the default.

When you update the model, the target database does not attempt an update that violates the relationship between these two tables.

This type of referential integrity is also called RESTRICTED.

Click to show or hide information. Cascade

Select to make the same changes to the foreign key as are made to the primary key when the parent in the selected relationship is updated.

Click to show or hide information. Set NULL

Select to set the entire foreign key to NULL when the parent in the selected relationship is updated.

When an update to the parent table of this relationship changes the value in the parent column, the value of this child column will be set to NULL.

Use this option to prevent a new parent value from applying automatically to all child cases.

This type of referential integrity is also called NULLIFIES.

Click to show or hide information. Set default

Select to set the entire foreign key to the default value when the parent in the selected relationship is updated.

When an update to the parent table of this relationship changes the value in the parent column, the value of this child column will be set to the default value.

Use this option to prevent a new parent value from applying automatically to all child cases.

Note You may select this option for columns that allow NULL values but do not have a default value specified because NULL is considered to be an unspecified default value.

Click to show or hide information. Do not enforce

Select to disable referential integrity when the parent in the selected relationship is updated.

On parent delete

Click to show or hide information. No action

Select to make no automatic changes to the foreign key when the parent in the selected relationship is deleted. This is the default.

This type of referential integrity is also called RESTRICTED.

Click to show or hide information. Cascade

Select to make the same changes to the foreign key as are made to the primary key when the parent in the selected relationship is deleted.

Click to show or hide information. Set NULL

Select to set all foreign key columns in matching rows to NULL when the parent in the selected relationship is deleted.

This type of referential integrity is also called NULLIFIES.

Click to show or hide information. Set default

Select to set all foreign key columns in matching rows to the default value when the parent in the selected relationship is deleted.

Note You may select this option for columns that allow NULL values but do not have a default value specified because NULL is considered to be an unspecified default value.

Click to show or hide information. Do not enforce

Select to disable referential integrity when the parent in the selected relationship is deleted.


Database Properties for Category shape (Definition)

Use these options to define a category's discriminator attribute on a parent table. Using a category establishes a foreign key relationship from the parent table to the child tables connected to the Category shape.

Category tables are subtypes of a parent table. For example, an employee's record (the generic parent) might contain a job-type column (the category discriminator); category entities in this example might include engineer, technician, and administrator.

If a category table contains all possible subtypes, it is considered complete; if it contains only some possible subtypes, it is considered incomplete.

Click to show or hide information. None

Select to set an available attribute as the category discriminator. By default, when a category is created, it does not have an attribute assigned to it as the category discriminator.

Click to show or hide information. This attribute

By default, an attribute is not selected as the category discriminator. You must select an attribute, or the model will fail to validate when you build it.

Click to show or hide information. Category is complete

Select this check box to indicate that the category (a subtype of its parent) is considered one of a finite set of children, all of which are represented in this model.

If you know that some branch of the parent leads to a child that you are not including in this model, clear this check box.


Database Properties for View shape (Join Criteria)

Click to show or hide information. Col 1

Lists all columns in Entity shapes that exist in the current diagram.

Click to show or hide information. Col 2

Lists all columns in Entity shapes that exist in the current diagram.

Click to show or hide information. Add

Click to specify that the data in the columns you've selected—in Col 1 and Col 2—will be used to create a view. Make sure you select columns that have common data (such as customer numbers), or else your view won't have any data to join.

Click to show or hide information. Remove

Click to delete the join criteria you selected under Criteria.

Click to show or hide information. Criteria

Shows the join criteria you created for the current diagram.


Database Properties for View shape (SQL)

Click to show or hide information. SQL

Shows the SQL code that is associated with the selected View shape.

Click to show or hide information. Auto-Generated

Select this check box to specify that Visio will automatically create the SQL code for you, and that the code will be used when you forward engineer the database.

Click to show or hide information. Code Properties

Click to open the Code Editor.


Database Properties for Type shape (Definition)

Use these options to create named and unnamed row types that act as reusable type definitions for tables or views.

Named row types are based on the SQL3 specification of tables, called typed tables, and are identified within the system as persistent type storage. Unnamed row types are not identified as persistent type storage.

Row types create composite data types that can be used in models that will be implemented on object relational databases. A row type can be specified as the data type.

Click to show or hide information. Name

Type a name for the composite type that will appear on the Data Type tab of the Column Properties dialog box and in the Types window.

By default, a suffix is added to the name to distinguish its type based on the selection under Composite type.

Click to show or hide information. Named row type

Select to specify that the type is not an alias of any other type.

Click to show or hide information. Distinct type

Select to specify that the type is based on another type and has the same representation as the type on which it is based, but is an entirely distinct type of its own.

If you select this option, you will not be able to select options on the Fields category.

Click to show or hide information. Domain

Select to specify that the type is an alias of another type; it is an indirect representation of the same type.

If you select this option, you will not be able to select options on the Fields category.

Note When you select Domain, the Alias Collection Type option becomes available.

Click to show or hide information. Alias collection type (visible only if Domain is selected)

Select an option to specify whether an attribute's value is a collection of a single value, set, list, or multi-set. In relational databases, all attribute collection types are single values. Object relational databases allow you to specify the additional collection types.

Single Value (Column) The collection type is populated by a single value. A query of a record with a single collection type returns a single value (such as 1, a, or string1).

Set The collection type is populated by a set of values of the same data type. A query of a record with a set collection type returns a set of values (such as {1, 2, 3, 4}, {a, b, c} or {string1, string2, string3}).

List The collection type is populated by a list of values of the same data type. A query of a record with a set collection type returns a list of values (such as (3, 2, 1, 4), (b, a, c) or (string2, string1, string3). Order is important here as opposed to sets and multi-sets where order is not assumed.

Multi-Set The collection type is populated by a multi-set of or bag of values of the same data type. A query of a record with a multi-set collection type returns a multi-set or bag of values (such as {1, 1, 2, 3, 3, 3, 3, 4}, {a, b, b, b, c} or {string1, string2, string2, string3}).


Database Properties for Type shape (Aliased Type)

Click to show or hide information. Unnamed Type

Specifies that the type is an unnamed row type. To specify an unnamed row type, choose Domain for Composite Type on the Definition tab, and then choose Unnamed on the Aliased Type tab.

Click to show or hide information. Named Type

Specifies a named row type and makes the list of types available.


Database Properties for Type shape (Notes)

Click to show or hide information. Notes

Type notes. The notes you type here are included in the logical model.


Database windows

Click to show or hide information. Code window

Lists all code for a particular database platform that is associated with your model, including code extracted from a database during reverse engineering. The Code window also provides access to the Code Editor, where you can revise existing code or write new code.

Click to show or hide information. Tables and Views window

Appears automatically when you use the Reverse Engineer Wizard, and lists the names of tables and views extracted from the database. You can drag elements from the Tables and Views window into your diagram, where you can view them along with any associated relationships.

Note You can view the tables related to a particular table without viewing the entire model diagram.

Click to show or hide information. Types window

Lists the composite types you defined, the built-in logical data types for your target database, and the portable data types you created using the User-Defined Types dialog box. Once you are familiar with the logical data type requirements of your database management system, you can assign data types to specific columns in the model.

Click to show or hide information. Output window

Displays feedback for certain actions, such as running the Reverse Engineer Wizard or validating the model. The Output window lists progress notes, errors, and warnings in tabbed panels. Use this information to quickly navigate to warning conditions or errors in the model.

Click to show or hide information. Verbalizer window

Provides information in text form that describes what the selected shape represents. For example, if you select an Entity shape in your diagram, the Verbalizer window displays information from the Database Properties window, such as the table name, column names, and information that you typed in the Notes tab.

Click to show or hide information. Project window

Displays a list of the files that are in a project. Because you can add any type of file to a project, the primary purpose of the Project window is to specify the source model documents. This window identifies source model documents by their document type (ER source model or ORM source model) and lists each source model under the appropriate subfolder.

Click to show or hide information. Object Types window (ORM)

Lists the object types that you have defined in an ORM conceptual model. You can drag elements from the Object Types window into your diagram, to include them in your model.

Click to show or hide information. Business Rules window (ORM)

The Business Rules window has two panes: All Folders on the left and Contents of <selected folder> on the right. You can create and manipulate folders in the left pane and work with fact types or object types in the right pane.

In the Contents pane, the Fact Types tab displays the existing fact types in your ORM model. Double-click a fact type to edit it in the Fact Editor. Double-click the last item in the list to add a new fact type to your ORM source model.

The Object Types tab displays the object types in your ORM model. Right-click an object type to add or delete object types, locate an object type in your diagram, and display the portable or physical data type for an object type.


Reverse Engineer Wizard

Click to show or hide information. About this wizard

Steps you through process of creating a connection to the target database you want to reverse engineer.

To create a connection, you specify an installed Visio driver and the 32-bit ODBC (Open Database Connectivity) data source. For convenience, before you run the wizard, you should set the default Visio driver and target database to the target database you want to reverse engineer.

The schema definition information that the wizard can extract depends upon a combination of things, such as the capabilities of the DBMS (Database Management System) and ODBC driver. To the extent that it is available from the target DBMS, you can select the schema definition information that the Reverse Engineer Wizard will extract.

Click to show or hide information. Installed Visio drivers

Displays the default Visio database driver.

Click to show or hide information. Setup

Click to display the Setup dialog box, where you can associate the selected Visio driver with an ODBC data source and set other driver preferences.

Click to show or hide information. Data Sources

If a data source has been defined on your system for the target database, then select it from the list of data sources.

If a data source has not been defined on your system for the target database, then click New to define a new data source.

Data source definitions reside on your system, but they can refer to databases on other systems.

Click to show or hide information. New

Click to display the Create New Data Source dialog box.


Reverse Engineer Wizard

Click to show or hide information. About this wizard

Steps you through process of creating a connection to the target database you want to reverse engineer.

To create a connection, you specify an installed Visio driver and the 32-bit ODBC (Open Database Connectivity) data source. For convenience, before you run the wizard, you should set the default Visio driver and target database to the target database you want to reverse engineer.

The schema definition information that the wizard can extract depends upon a combination of things, such as the capabilities of the DBMS (Database Management System) and ODBC driver. To the extent that it is available from the target DBMS, you can select the schema definition information that the Reverse Engineer Wizard will extract.

Click to show or hide information. Tables

Select this check box to extract tables from the target database.

Click to show or hide information. Primary Keys

Select this check box to extract primary keys from the target database.

Click to show or hide information. Indexes

Select this check box to extract indexes from the target database.

Click to show or hide information. Foreign Keys

Select this check box to extract foreign keys from the target database.

Click to show or hide information. Triggers

Select this check box to extract triggers, including trigger code, from the target database.

Click to show or hide information. Check Clauses

Select this check box to extract check clauses, including check-clause code, from the target database.

Click to show or hide information. Views

Select this check box to extract views from the target database.

Click to show or hide information. Stored Procedures

Select this check box to extract stored procedures, including procedure code, from the target database.


Reverse Engineer Wizard

Click to show or hide information. About this wizard

Steps you through process of creating a connection to the target database you want to reverse engineer.

To create a connection, you specify an installed Visio driver and the 32-bit ODBC (Open Database Connectivity) data source. For convenience, before you run the wizard, you should set the default Visio driver and target database to the target database you want to reverse engineer.

The schema definition information that the wizard can extract depends upon a combination of things, such as the capabilities of the DBMS (Database Management System) and ODBC driver. To the extent that it is available from the target DBMS, you can select the schema definition information that the Reverse Engineer Wizard will extract.

Click to show or hide information. Select tables and/or views to reverse engineer

Select the tables that you want to extract from the target database.

Click to show or hide information. Select All

Click to select all the check boxes in the list.

Click to show or hide information. Deselect All

Click to clear all the check boxes in the list.


Reverse Engineer Wizard

Click to show or hide information. About this wizard

Steps you through process of creating a connection to the target database you want to reverse engineer.

To create a connection, you specify an installed Visio driver and the 32-bit ODBC (Open Database Connectivity) data source. For convenience, before you run the wizard, you should set the default Visio driver and target database to the target database you want to reverse engineer.

The schema definition information that the wizard can extract depends upon a combination of things, such as the capabilities of the DBMS (Database Management System) and ODBC driver. To the extent that it is available from the target DBMS, you can select the schema definition information that the Reverse Engineer Wizard will extract.

Click to show or hide information. Select Stored Procedures To Reverse Engineer

Displays all the stored procedures that you can extract from the target database.

Click to show or hide information. Select All

Selects all the stored procedures in the list.

Click to show or hide information. Deselect All

Cancels the selection of all stored procedures that have been selected in the list.


Reverse Engineer Wizard

Click to show or hide information. About this wizard

Steps you through process of creating a connection to the target database you want to reverse engineer.

To create a connection, you specify an installed Visio driver and the 32-bit ODBC (Open Database Connectivity) data source. For convenience, before you run the wizard, you should set the default Visio driver and target database to the target database you want to reverse engineer.

The schema definition information that the wizard can extract depends upon a combination of things, such as the capabilities of the DBMS (Database Management System) and ODBC driver. To the extent that it is available from the target DBMS, you can select the schema definition information that the Reverse Engineer Wizard will extract.

Click to show or hide information. Tables

Lists all the tables that you selected to be reverse engineered.

Click to show or hide information. Catalog info

Lists all the database structures that you selected to be reverse engineered.


Refresh Model Wizard

Click to show or hide information. About this wizard

Steps you through connecting to your target database, comparing the model diagram to the actual database's schema, and then resolving any conflicts by updating the model.

If the wizard detects differences between the model and database, you can either update the model or ignore the conflict for now.

Click to show or hide information. Installed Visio drivers

Displays the default Visio database driver.

Click to show or hide information. Setup

Click to display the Setup dialog box, where you can associate the selected Visio driver with an ODBC data source and set other driver preferences.

Click to show or hide information. Data sources

If a data source has been defined on your system for the target database, then select it from the list of data sources.

If a data source has not been defined on your system for the target database, then click New to define a new data source.

Data source definitions reside on your system, but they can refer to databases on other systems.

Click to show or hide information. New

Click to display the Create New Data Source dialog box.


Refresh Model Wizard

Click to show or hide information. About this wizard

Steps you through connecting to your target database, comparing the model diagram to the actual database's schema, and then resolving any conflicts by updating the model.

If the wizard detects differences between the model and database, you can either update the model or ignore the conflict for now.

Click to show or hide information. List

Lists all conflicts between your model and the database whose structure it represents. To resolve each conflict, you must select it and then click one of the following options:

  • No change
  • Refresh model—Changes the model to match the database.


Refresh Model Wizard

Click to show or hide information. About this wizard

Steps you through connecting to your target database, comparing the model diagram to the actual database's schema, and then resolving any conflicts by updating the model.

If the wizard detects differences between the model and database, you can either update the model or ignore the conflict for now.

Click to show or hide information. List

Lists the changes that will be made to your model to synchronize it with the target database.

You can expand the branches of the tree to see details.


Reverse Engineer Wizard

Click to show or hide information. About this wizard

Steps you through process of creating a connection to the target database you want to reverse engineer.

To create a connection, you specify an installed Visio driver and the 32-bit ODBC (Open Database Connectivity) data source. For convenience, before you run the wizard, you should set the default Visio driver and target database to the target database you want to reverse engineer.

The schema definition information that the wizard can extract depends upon a combination of things, such as the capabilities of the DBMS (Database Management System) and ODBC driver. To the extent that it is available from the target DBMS, you can select the schema definition information that the Reverse Engineer Wizard will extract.

Click to show or hide information. Yes, add the shapes to the current page

Click to have Visio automatically add the reverse engineered items to the current page.

Click to show or hide information. No, I will add the shapes later

Click to add the reverse engineered items later by manually dragging them from the Tables and Views window onto the desired page.